Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Formula To Search Cells/columns Containing Specific Text

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi,

I would like to search on 2 columns, to see if each cell in Column B contains text from any cell in Column A.

For example, IF the text in Column B (Email Address), Row 2 contains text from any cell in Column A (Domain) = True. Below is an example:

(A) DOMAIN NAME | (B)EMAIL ADDRESS | (C)TRUE OR FALSE?
hotmail.com | kara@web.com | TRUE
msn.com | mike@site.com | TRUE
web.com | jess@myaddy.com | FALSE
domain.com | sara@aol.com | TRUE
aol.com | ted@domain.com | TRUE
site.com | mark@domain.com | TRUE

I am currently using this formula:
=IF(ISNUMBER(SEARCH($A:$A,E2)),"TRUE","FALSE")

but it doesn't appear to be working. It only works if the matching rows are adjacent to each other. As you can see from the example above, the matching domains could be in any row, any cell.

Would anyone know the correct formula for this? I've searched everywhere Thank you!

View Answers     

Similar Excel Tutorials

Count the Number of Cells that Start or End with Specific Text in Excel
How to count cells that match text at the start or the end of a string in Excel. If you want a fuller explanation o ...
Count the Number of Cells that Contain Specific Text in Excel
How to count the number of cells that contain specific text within a spreadsheet in Excel. I'll show you how to cou ...
Delete All Rows that Contain a Specific Value in Excel
Quickly find all rows in Excel that contain a certain value and then delete those rows. This is a simple technique ...
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular ...

Helpful Excel Macros

Determine a Cell's Color with this UDF - Outputs as Text or the Index Number in Excel
- This free Excel UDF allows you to output the color of a cell in text format or as that color's index number. Also note
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se

Similar Topics







I have a bunch of domain names (including subdomains) in column B

In column A i need just the raw domain name...

example

a1 [domain.com]
b1 [domain.com]

a2 [domain.com]
b2 [mail.domain.com]

a3 [domain.com]
b3 [subdomain.domain.com]

etc...

thanks


I am using the following spreadsheet. Column K (Grace Range) looks at Column B (GRC) and returns a TRUE or FALSE statement based on the following formula: =IF((B2>=45)*(B2=28 and FALSE TRUE FALSE 37 55 4 10 0 0 TRUE TRUE TRUE TRUE TRUE TRUE FALSE 10 17 5 10 0 0 TRUE TRUE TRUE TRUE FALSE TRUE FALSE 2 16 5 10 0 0 TRUE TRUE TRUE TRUE FALSE TRUE FALSE 19 34 5 10 0 0 TRUE TRUE TRUE TRUE FALSE TRUE FALSE 6 29 8 10 0 0 TRUE TRUE TRUE TRUE FALSE TRUE FALSE 32 8 10 0 0 TRUE TRUE TRUE TRUE FALSE TRUE FALSE 25 8 10 0 0 TRUE TRUE TRUE TRUE FALSE TRUE FALSE


I have a vendor list of over 3,000 email addresses. Some of the cells contain multiple email addresses that look like this:

John Doe ; Jane Doe jane.doe@domain.com;

I want to know if there is a macro/vbe code or formula that could separate the full names from the email address and span it over three columns like this:

Column 1 Column 2 Column 3
Doe, John Doe, J john.doe@domain.com

To make matters worse, some of vendors may have four different email addresses such as j.doe@domain.com; johndoe@domain.com; doejohn@doman.com, etc.

I would appreciate any suggestions. Thanks


Hi guys,

I am facing a challenge with a DB. One of the columns contains the email
addresses. I would like to find a way to delete the rows that contain in the
email address field a certain domain name. For example, in that column, there
are several email addresses containing the same domain name. And I need to
delete all the rows containing that domain name. Will I need to use a macro??

Many thanks,
Cristi



I was trying to help another board member with their question, but it led me to a question of my own. I am trying to get this array to work:
Code:

{=LARGE(ROW($A$1:$A$234)*NOT(ISERR(INDIRECT("Sheet1!Assets"&(ROW($A$1:$A$234))))),1)}


I named a range Assets234. I used ROW($A$1:$A$234) to return an array of numbers. That was then concatenated with "Sheet1!Assets". The INDIRECT function would then turn these into references. Since some of these aren't legitimate references I received a #REF! error. I then used the ISERR function to determine which of the references in the array evaluated to an error. I needed to multiply ROW($A$1:$A$234) against the results and ISERR had the opposite results I needed so I used NOT() to change the TRUEs to FALSEs and vice versa. Knowing that Excel sees False as 0 and True as any other number I multiplied the two arrays. Since everything was 0 except for the last value the LARGE() function should have returned 234. Instead it returns 0. Does anyone know why I get these results? Here is the formula evaluated one step at a time (I did my best to wrap them to the screen so you wouldn't have to browse to the right):

Code:

{=LARGE(ROW($A$1:$A$234)*NOT(ISERR(INDIRECT("Sheet1!Assets" &(ROW($A$1:$A$234))))),1)}


Code:

=LARGE(ROW($A$1:$A$234)*NOT(ISERR({#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;  #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!; #REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;2})),1)


Code:

=LARGE(ROW($A$1:$A$234)*NOT({TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE; FALSE}),1)


Code:

=LARGE(ROW($A$1:$A$234)*{FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE},1)


Code:

=LARGE({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21; 22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42; 43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63; 64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84; 85;86;87;88;89;90;91;92;93;94;95;96;97;98;99;100;101;102;103; 104;105;106;107;108;109;110;111;112;113;114;115;116;117;118; 119;120;121;122;123;124;125;126;127;128;129;130;131;132;133; 134;135;136;137;138;139;140;141;142;143;144;145;146;147;148; 149;150;151;152;153;154;155;156;157;158;159;160;161;162;163; 164;165;166;167;168;169;170;171;172;173;174;175;176;177;178; 179;180;181;182;183;184;185;186;187;188;189;190;191;192;193; 194;195;196;197;198;199;200;201;202;203;204;205;206;207;208; 209;210;211;212;213;214;215;216;217;218;219;220;221;222;223; 224;225;226;227;228;229;230;231;232;233;234}*{FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; FALSE;TRUE},1)


Code:

=LARGE({0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0; 0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0; 0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0; 0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0; 0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0; 0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0; 0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;234},1)


Code:

=234





I have been manually using Excel to manage my Outlook blocked senders
list but it's gotten so large that I want to automate this task. I
thought that I could learn enough VBA to do this on my own but I can't
manage it. If someone would be willing to help, here's what I want to
accomplish:

Given
Worksheet with two columns
Each row represents an email address
Col A has the part of the email address before the @
Col B has the domain
An empty cell in Col A designates the entire domain
Worksheet is sorted in domain order

Goals
Where this worksheet contains multiple addresses from the same domain,
delete duplicate rows and blank col A for the remaining row.

After deleting the dups, I concatenate Col A + "@" + Col B and import
back into Outlook's blocked senders list.

--
Bob Simon
remove both "x"s from domain for private replies



Hello All,

I am trying to think of the best way to implement this process. I believe a LEFT SEARCH will be best but please let me know if this is possible.

In column A I have a few different web addresses that end in .com, .edu, .org etc. After the domain extension is a really long web address. Is there any way I can have a formula in Column B that looks at the web address in column A and shortens the URL to that domain extension like .com, .edu etc?

This should not be that hard. :x

I'm trying to write an error catching formulas read reads TRUEs and FALSEs from 4 columns. You can't have a TRUE in the next column until there is a TRUE in the first. For example...

TRUE FALSE FALSE FALSE = OK
FALSE TRUE FALSE FALSE = Bad
TRUE TRUE FALSE TRUE = Bad
TRUE TRUE FALSE FALSE - OK etc. etc.

Figured a straight forward formaula with AND s and ORs with an IF at the beginning would work fine. But noooooo, I can't get it to work. Could somebody tell me what I'm doing wrong? If I had done it correctly, rows 5,6,7, and 9 should have come back with 'ERROR'. The last row has a much simpler formula to test and it doesn't even work. Arg.

******** ******************** ************************************************************************> Microsoft Excel - ModelTrack.xls ___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout J8 J9 J10 J11 J12 J13 J14 J15 J16 =
F G H I J 7 Preliminary Vendor
Information Verified IFC Error 8 TRUE TRUE TRUE TRUE
9 TRUE TRUE TRUE FALSE
10 TRUE TRUE FALSE FALSE
11 TRUE FALSE FALSE FALSE
12 TRUE FALSE FALSE FALSE
13 TRUE FALSE TRUE FALSE
14 FALSE TRUE TRUE TRUE
15 TRUE FALSE FALSE FALSE
16 FALSE TRUE FALSE TRUE
Equipment
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


Hello EF! I'm new here and even if no one can help, this is a great resource. So thanks already!

Anyway, here's my situation...

I'm using Excel Mac 2011 and have a long list of e-mail addresses (2000+).

I need to import these to my e-mail broadcasting service, however, only some can be read because the addresses look like this:

"email@domain.com <email@domain.com>"

This text is in single cells.

I'm looking for a way (rather than manually) to remove the "<email@domain.com>" part of the text in each cell, automatically.

It's quite a long list, so this would really help to save time.

I looked at the replace tool and have also wondered if there is a some kind of function I can run to achieve this. I'm just not sure where to start!

Any help, much appreciated.

Cheers,

Matt


Anyone help me with this one?

I'm using the following formula to look in Column N and indicate TRUE if the string "at home" is any place in the column. Then I sort the column and parse out the TRUE records.

=ISNUMBER(SEARCH("at home",N2))

What I would like to do is be able to look for several strings at one time and have the formula indicate TRUE or FALSE if any one or more of the strings are present. (FALSE to display if none of the strings are present.)

Example: "at home" "from home" "home based" "opportunity"

What I'm doing is extracting ads from online directories, and then parsing out the ones that are related to working from home. There may be a few hundred words in the listing. I want have the formula check all the text and indicate TRUE if any of the chosen words or phrases are present.

I can put the formula in several columns and then sort each column. That's a PIA though. Doing all at one time would be much better.

Thanks

Dale


Hi - Trying to cleanup some free form user company name entries by using the domain off the user's email address to build a lookup list & standardize the company name.

Does anyone know how you would write a formula to extra the domain from an email address?

All I really want to do is delete all before the @ character.


I need a macro to take an email address, strip off the "@" and full domain name. The length (and the domain name) will not be known. Example: jsmith@company.com to become just jsmith.

This will be used in other macros within my workbook and each entry could be different so a simple ctrl H on the column cannot be used.



I have one column i'd like to edit so it removes @domain.co.uk from every cell.

A
Server1@domain.co.uk
Server2@domain.co.uk
Server3@domain.co.uk
Server4
Server5
Server6@domain.co.uk
Server7@domain.co.uk

So it just displays:

A
Server1
Server2
Server3
Server4
Server5
Server6
Server7

Please could some one suggest the best way to do this - there are thousands of records I need to apply this to.

many thanks




Hi All,

I had a list of email addresses and I need to extract only the domain type from the list. And I am looking for a formula to extract it.

Example:-

rmehra@universalaviation.aero

I need to extract only extract only " aero" from the email. Here I need to extract domain from right after dot(.) from the email address.

Please find the sample file.

Can anyone tell me why this function will not find "street"

HTML Code:

=IF(OR(IF(ISERR(SEARCH("Crossway",B4,)),FALSE,TRUE),IF(ISERR(SEARCH("Street",B4,)),FALSE,TRUE),IF(ISERR(SEARCH("Crescent",B4)),FALSE,TRUE),IF(ISERR(SEARCH("Boulevard",B4)),FALSE,TRUE),IF(ISERR(SEARCH("East",B4)),FALSE,TRUE),IF(ISERR(SEARCH("North",B4)),FALSE,TRUE),IF(ISERR(SEARCH("South",B4)),FALSE,TRUE),IF(ISERR(SEARCH("West",B4)),FALSE,TRUE),IF(ISERR(SEARCH("Lane",B4)),FALSE,TRUE),IF(ISERR(SEARCH("trail",B4)),FALSE,TRUE)),"Exception  Error","")





Hello,

There is a question from the following link:

http://www.mrexcel.com/forum/showthread.php?t=425898

Mr. Erik gave a solution to that problem as follows

D2:D25

{=IF(ROW(A1)>COUNTIF($A:$A,TRUE),"",INDEX($A$2:$B$25,SMALL(IF($A$2:$A$25=TRUE,ROW($A$2:$A$25)-1),ROW(A1)),COLUMN(B1)))}

A B C D
1 OK VALUE TRUE VALUES
2 TRUE AAA AAA
3 FALSE BBB EEE
4 FALSE CCC FFF
5 FALSE DDD III
6 TRUE EEE LLL
7 TRUE FFF OOO
8 FALSE GGG QQQ
9 FALSE HHH RRR
10 TRUE III SSS
11 FALSE JJJ TTT
12 FALSE KKK
13 TRUE LLL
14 FALSE MMM
15 FALSE NNN
16 TRUE OOO
17 FALSE PPP
18 TRUE QQQ
19 TRUE RRR
20 TRUE SSS
21 TRUE TTT
22 FALSE UUU
23 FALSE VVV
24 FALSE WWW
25 FALSE XXX

If I remove the first row that includes the headings, and if I want to start directly with TRUEs and FALSEs, the formula will not work

Thank you


Hi Everyone,

I have a sheet with First Name, MI, and Last Name in Columns A, B, and
C and company domain in column D. I would like figure out how to
combine these into an email address in column E that reads
FirstinitialLastName@"Domain" so Mel Kiper at Microsoft.com becomes
mkiper@microsoft.com. Ideally I would like to maybe make macros for
common email protocols as well. Any Ideas?

Regards,

Reece




Hello All,

I'm back and needing some assistance. I tried to do a Search on this to see if there was already an answer but wouldn't work. So here we go.

What I'm trying to do is the following:

I want to compare two cells w/ text in them and want to return a text result based off of a particular text string. May sound confusing but will try and draw it.

The equation I have so far is

=IF(SEARCH("TS",B9), IF(SEARCH("TS",C9),"YY","YN"), IF(SEARCH("TS",C9),"NY","NN"))

What this is doing is looking into cell B9 to find "TS", if that is true then will look into C9 for "TS" and if that is true, will display YY in the cell. This part of the formula works.

However, when I put something other than a TS or leave it blank in B9 or C9...the result is #VALUE!

The problem that I'm having is that I need the result to be YN or NY or NN. Anything w/ a false result won't work.

Hope you understand what I'm asking...any assistance will be good.

Thanks,

Jason


I use a web app to produce a survey. It gives me an output in a csv. The check box's output is True or False .

I need to create a VBA macro that looks at the specific columns, my columns change order, and if the column has a True statement it puts a specific number in Column Code1 based on the column that was true .

All of the numbers will need to be concatenated together in the column, each number is seperated by a space. If a column has a False statement, it does not put a number on Code1.

See the table below for an example layout.

ValueA1 if True = 1
ValueB1 if True = 2
ValueC1 if True = 3
ValueD1 if True = 4
ValueE1 if True = 5
ValueF1 if True = 6
ValueG1 if True = 7
False = no number in Code1 column.



ValueA1 ValueB1 ValueC1 ValueD1 ValueE1 ValueF1 ValueG1 Code1 TRUE FALSE FALSE TRUE FALSE FALSE TRUE 1 4 7 FALSE FALSE TRUE FALSE TRUE FALSE TRUE 3 5 7 TRUE TRUE FALSE FALSE TRUE FALSE FALSE 1 2 5 FALSE TRUE FALSE FALSE FALSE TRUE TRUE 2 6 7 FALSE FALSE FALSE TRUE FALSE TRUE FALSE 4 6







Let me say how awesome this forum is! Everyone has been emmensely helpful in helping me with my questions! It is really appreciated!


I have hundreds of employee e-mail addresses in our company but no domain in excel file. If I want to create a group list for these people i need the domain. The domain is constant. Is there a way I can add the Domain (say @dontknow.com) without going thru line by line?


I will try to keep this as simple as possible.

On Sheet1, I have lists of text values, in several columns. The lists (seperated by columns) are not related to eachother.
Example:

LONDON .. PARIS ... TOKYO
dogs .... cats .... dogs
cats .... wolves .. horses
sheep ............. sheep

On Sheet2, the first column is a single list of all the animals. The rest of the columns are the cities - one city for each column.
Example:

ANIMALS .. LONDON .. PARIS .. TOKYO
cats
dogs
horses
sheep
wolves

The end goal here is to populate the second sheet, with 'True' or 'False'. The first cell to work out would be;
If *LONDON* contains the text *CATS*, then 'TRUE'. The information is in Sheet1, and the answer is True.

I have the following formula;
=IF(COUNT(SEARCH($A2,Sheet1!A:A)),"True","False")

This is currently working, but only for the first row. All the cells in lower rows just return "False" (where many should be 'True').

One of the cells on the first row that did return 'True' correctly, was column 'O'. As a test, i copied the exact same formula into the cell below (row 3) so it was looking at row 2 (exactly the same formula), and it returned 'False'.

So, what could be the problem with this formula that means it only works on the first row?

Please ask if anything is unclear. Any suggestions/thoughts appreciated. TIA!


A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1 OK 2 OFFICE ALTRONICS 3 MIKE 3L PHILIPPINES 4 WILLIE RUSTANS 5 BEN ABCED 6 RYAN EH SISON 7 JOSEPH DIAMOND HOTEL 8 MIKE LCC TRUE FALSE 9 10 11 NAME COMPANY BOTH NAME & COMPANY COMPANY 12 OFFICE ALTRONICS OK OK TRUE TRUE FALSE TRUE FALSE TRUE FALSE OFFICE ALTRONICS 13 MIKE 3L PHILIPPINES OK OK TRUE TRUE FALSE TRUE FALSE TRUE FALSE MIKE 3L PHILIPPINES 14 WILLIE RUSTANS OK OK TRUE TRUE FALSE TRUE FALSE TRUE FALSE WILLIE RUSTANS 15 BEN ABCED OK OK TRUE TRUE FALSE TRUE FALSE TRUE FALSE BEN ABCED 16 RYAN EH SISON OK OK TRUE TRUE FALSE TRUE FALSE TRUE FALSE RYAN EH SISON 17 JOSEPH DIAMOND HOTEL OK OK TRUE TRUE FALSE TRUE FALSE TRUE FALSE JOSEPH DIAMOND HOTEL 18 MIKE LCC OK OK TRUE TRUE FALSE TRUE FALSE TRUE FALSE MIKE LCC 19 MIKE LCC OK OK TRUE TRUE FALSE TRUE FALSE TRUE FALSE MIKE LCC 20 RYAN EH SISON OK OK TRUE TRUE FALSE TRUE FALSE TRUE FALSE RYAN EH SISON 21 OFFICE TRADE ONE OK OK FALSE TRUE TRUE FALSE TRUE FALSE FALSE OFFICE TRADE ONE 22 BEN ABCED OK OK TRUE TRUE FALSE TRUE FALSE TRUE FALSE BEN ABCED 23 24 TRUE TRUE TRUE 25 TRUE TRUE TRUE 26 TRUE TRUE TRUE 27 TRUE TRUE TRUE 28 TRUE TRUE TRUE 29 TRUE TRUE TRUE 30 TRUE TRUE TRUE 31 TRUE TRUE TRUE 32 TRUE TRUE TRUE 33 TRUE TRUE TRUE 34 TRUE TRUE TRUE

GUD DAY!.

I HAVE A FORMULA IN F12:

=IF(B12="","",SUMPRODUCT(--($A$2:$A$8=A12),--($B$2:$B$8=B12))>0).

THAT FORMULA RETURN TRUE OR FALSE,


THEN IN P12:Q22 IS JUST A DUPLICATE. P12: =A12, Q12: =B12, AND SO ON.

A12:B22. HAVE A VALUE, ITS JUST I USED CONDITIONAL FORMATTING.

ITS WORKING BUT I NEED TO CHANGE THE RANGE TO P12:Q22 IN CELL F12. SO NOW THE FORMULA OF F12 IS

=IF(B12="","",SUMPRODUCT(--($P$12:$P$22=A12),--($Q$12:$Q$22=B12))>0).

WHICH IS THE RANGE I DUPLICATE.

AND NOW ITS NOT WORKING. ALWAYS FALSE EVEN IT SHOULD BE TRUE VALUE...


PLS HELP ME

I USE =CELL TO DUPLICATE SO

WHAT FUNCTION SHOULD I USE TO DUPLICATE TO MAKE THIS FORMULA WORKS?

=IF(B12="","",SUMPRODUCT(--($P$12:$P$22=A12),--($Q$12:$Q$22=B12))>0).


Hoja1

  A B 1 c 4 2 d 3 3 ba 5 4 z 1 5 r 2 6 a 6 7   21 8     9     10 c 21 11 d   12 ba   13 z   14 r   15 a  
Spreadsheet Formulas Cell Formula B1 =SUMPRODUCT(N(A1<=$A$1:$A$6)) B2 =SUMPRODUCT(N(A2<=$A$1:$A$6)) B3 =SUMPRODUCT(N(A3<=$A$1:$A$6)) B4 =SUMPRODUCT(N(A4<=$A$1:$A$6)) B5 =SUMPRODUCT(N(A5<=$A$1:$A$6)) B6 =SUMPRODUCT(N(A6<=$A$1:$A$6)) B7 =SUM(B1:B6) B10 {=SUMPRODUCT(N(IF(SUBTOTAL(3,OFFSET($A$1:$A$6,ROW($A$10:$A$15)-ROW($A$10),0,1)),$A$1:$A$6)<=TRANSPOSE($A$1:$A$6)))} Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4

My question: How to obtain an Array into my Formula 1 x 6 or 6 X1, with the Partial sums of each boolean-Row?

See:
SUMPRODUCT(N({TRUE;TRUE;FALSE;TRUE;TRUE;FALSE\ = 4
FALSE;TRUE;FALSE;TRUE;TRUE;FALSE\ = 3
TRUE;TRUE;TRUE;TRUE;TRUE;FALSE\ = 5
FALSE;FALSE;FALSE;TRUE;FALSE;FALSE\ = 1
FALSE;FALSE;FALSE;TRUE;TRUE;FALSE\ = 2
TRUE;TRUE;TRUE;TRUE;TRUE;TRUE\})) = 6


I want : {4\3\5\1\2\6}
and then I will can to apply LARGE or SMALL functions


Hello,
I hope someone can help me! I am entering contact information in a spreadsheet. One column is for email addresses. Every email address has the same domain name. I am tired of typing "@blahblahblah" over and over, and I'd like to make an input mask for this column that will allow me to simply type the beginning of the address. This sheet will eventually have about 2000 rows, and this would save 15 keystrokes per row. ! Any suggestions?
Thanks for your help!
Va




I have a limited background in VB and am looking for an easy way to sort through a column containing upwards of 800 cells. Each cell has a bunch of irrelevant information mixed around the email address I'm looking to pull from the cell.

Example (cell A1):

Sample message used here to show info|
More sample information that I do not need|
You get the point, I don't need this information|
email@domain.com 8282013 random information here

All I need in cell B1 would be: email@domain.com

All help would be greatly appreciated!