|
Formula To Search Cells/columns Containing Specific Text
|
|
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Formula To Search Cells/columns Containing Specific Text - Excel
|
View Answers
|
|
|
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!
Similar Excel Video Tutorials
Delete All Invoices w TBO
- Delete All Invoice Records That Contain an ID with "TBO" using the SEARCH function and Go To Formula Numbers. SEARCH("TBO",&q ...
SUMIF function formula 21 Examples
- See methods for summing with criteria: specific words, specific numbers, numbers greater than a hurdle, numbers between two values, values than are NO ...
Similar Topics
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!
How can I create a link that will send mail to multiple reciepients, with out
getting a pop-up telling me that the formula is too long?
Example:=HYPERLINK("mailto:name@domain.com,name@domain.com,name@domain.com,name@domain.com,name@doma in.com,name@domain.com,name@domain.com,name@domain.com,name@domain.com,name@domain.com,name@domain.c omname@domain.com,name@domain.com,name@domain.com,name@domain.com,name@domain.com,name@domain.com,na me@domain.com,name@domain.com,name@domain.com,name@domain.com,,"family")
I have been looking on forums and using Google searches for the better part of a day now and cannot seem to get this working. I have gotten close, but not quite what I need. What I need to do is compare a cell containing text with against a column that contains text on a different sheet.
For example in cell A1 I have text "hostname"
In another sheet I have a column of hostnames in format of "hostname.domain.com" or a variety of text tacked on to the text from A1. So I really need the formula to look for my text string anywhere in the text of the cell range.
I want the cell with the formula to give a true/false if the text in A1 is found in that other column. The search needs to be case insensitive.
The closest I got after trying to use MATCH, SEARCH, FIND was:
=VLookup(A1,'Sheet1'!$G$3:$G$3779,1,False)
But that is for an exact match which does not work as it isn't exact and doesn't really return true/false.
Any help would truly be appreciated. Thank you.
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
Hello,
I am totally stuck on how to do this problem.
I have a sheet with a list of domain names in column "A" and the days since the domain was entered into the sheet in column "B" as well as colum C which has a yes / no selection.
What I want to be able to do is to have another area on the same worksheet which will show the domains that are over 4 days old and selection in column "c" is "no".
I have tried this but the following happens.
domain 1
0
0
domain 2
0
0
domain 3
Is there anyway to modify this so that it shows the following instead.
domain 1
domain 2
domain 3
Currently using the following formula
If(AND(B1>4,C1="N"),A1,0)
Ideally the results will be in a box further down the spreadsheet, also is there anyway to auto calculate the new results once some rows are deleted.
Thanks in advance
Ian
I am an amateur in programming. I will try to explain what I need help with. I have over 800 email addresses, many of which are from the same domain (@DomainName.com) but are preceded by a name or an abbreviation for a person's name. So, I deleted the duplicates from column B and now have 200 unique Domain Names. Check this out...
A B C
Name DomainName
1 Peter Pan NNL.com
2 P.Pan PB.com
...so on and so forth.
I need a formula that I can express the following function/argument...
IF B1="NNL", then C1="Never Never Land Inc." else if B1="PB.COM", then B1="Peanut Buter LLC"...and so on, 200 times...one ELSE IF PER DomainName...
Basically I want to run a check for all 200 domain names until the statement is true then I want it to return the respective CompanyName. I have been fiddling around for 5 hours...still stumped...
Hi everyone. I expect this query has been answered here before, but I am not sure of the right terminology to use so I haven't been able to find a solution via the search. My problem is this. I have a two column data base:
Property, Imagepath
A, domain.com/imageA1.jpg
A, domain.com/imageA2.jpg
A, domain.com/imageA3.jpg
B, domain.com/imageB1.jpg
B, domain.com/imageB2.jpg
B, domain.com/imageB3.jpg
What I want is to group it by property, so I get
Property, Image1, Image2, Image3
A, domain.com/imageA1.jpg, domain.com/imageA2.jpg, domain.com/imageA3.jpg
B, domain.com/imageB1.jpg, domain.com/imageB2.jpg, domain.com/imageB3.jpg
Is this possible in Excel?
Thanks in advance.
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 Everyone,
I have a load of cells that contain email addresses, and i need to split these addresses down, to show only the domain names.
Is there a quick way of doing this, or do i need to click into every cell and delete the 1st half of the address?
Many Thanks
James
Hi Everyone,
I have a load of cells that contain email addresses, and i need to split these addresses down, to show only the domain names.
Is there a quick way of doing this, or do i need to click into every cell and delete the 1st half of the address?
Many Thanks
James
James try text to column option in data tools to split the cells.
tek9step
Hi james_edstrom
try...
Code:
Option Explicit
Sub ptest()
Dim rng As Range, p As Range
Set rng = ActiveSheet.Range("B1:B12")
With CreateObject("VBScript.RegExp")
.MultiLine = False
.Global = False
.IgnoreCase = True
.Pattern = "^([a-z0-9_\.\-]+)\@(([a-z0-9\-]+\.)+)([a-z]+)$"
For Each p In rng
If .Test(p) = True Then
p.Offset(0, 1) = "Valid"
p.Offset(0, 2) = .Replace(p, "$1")
p.Offset(0, 3) = .Replace(p, "$2")
p.Offset(0, 4) = .Replace(p, "$4")
Else
p.Offset(0, 1) = "InValid"
End If
Next
End With
End Sub
adjust ranges to suit
Hi,
How about this:
=RIGHT(A1,LEN(A1)-FIND("@",A1))
or use data > text to columns > delimited > custom > @
oopsie, there's some double posting going on. Mods, please merge?
oopsie, there's some double posting going on. Mods, please merge?
Thanks everyone, very helpful!
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 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 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
Hi there,
I'm currently using Windows XP and Excel 2007 and was wondering if anyone can help me with the following filtering issue?
Essentially, in my spreadsheet, I have the following data:
domain.com
domain.com/
domain.com/domain
domain.com/domain/games
a-domain.com
a-domain.com/
However, I'd only like to count the first reference to domain.com and a-domain.com, and get Excel to filter the rest.
Is there anything I can use to automatically remove "/sub-folder/sub-page" references from my spreadsheet, so after filtering, I'd be left with the following?:
domain.com
a-domain.com
Any help you can offer on this issue would be much appreciated - If you require any more information from me, then just let me know!
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
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?
Hi
I would like to make a spread sheet where i enter the following text
FREE DOMAIN NAME
FREE DOMAIN NAME
FREE DOMAIN NAME
FREE DOMAIN NAME
FREE DOMAIN NAME
FREE DOMAIN NAME
FREE DOMAIN NAME
Which scools down about 100+ but then i would like the spread sheet to be able to add the letter 1 after then the number 2 and so on like this
FREE DOMAIN NAME 1
FREE DOMAIN NAME 2
FREE DOMAIN NAME 3
FREE DOMAIN NAME 4
FREE DOMAIN NAME 5
FREE DOMAIN NAME 6
FREE DOMAIN NAME 7
And so on upto about 4000, is there a way i can do this as i don want to enter each number in manually
Thanks
Kerr
Hi,
I need to count the number of cells which is matching with my criteria form two columns. But my requirement is, if it is matching with the criteria of 1st cell of 1st column, then only it should match the criteria for 1st cell of 2nd column.
For exp:
Tom True
Can False
Can False
Tom True
Tom False
Tom False
So I need to check how many times, "Tom" is having "True" values. In this case, the value is "2".
I found one formula but it is not working:
=SUM(IF((A1:A6="Tom")-(B1:B6="True"),1,0))
It is giving result as 4, instead of 2.
Can anyone give me any suggestion to get the result.
Thanks & Regards,
Pramod
Hello,
Column 'A' I have a list of domains names with column B information connected to each domain.
Column 'F' I also have a list of domain names and next to these domain names are information about them in columns G, H and I.
I want to Match the domain name in column A (and its partner in column B) next to the same domain name in column F without moving F, G, H and I columns.
As there are more domains in column F than column A the final result would show many blank rows between domains in column A and B.
*** Some domains in A are not found in F at all. How will this affect things?
Hope this makes sense! See attached part list.
Thanks for any help. I use Excel 2007.
Regards
james
Dear Friends,
Looking for a bit of assistance with a formula that ought (i think) to look something like this:
=IF(ISNUMBER(SEARCH("Action Adventure", D2)), 0, IF(ISNUMBER(SEARCH("Action RPG", D2)), 0, IF(ISNUMBER(SEARCH("Action", D2)),1,0)))
What I want here is a query of the text in cell D2 such that the complete phrases "Action Adventure" and "Action RPG" yield a FALSE value of 0 while the term "Action" alone dings TRUE, resulting in a 1.
I've tried a lot of permutations on this; hopefully someone can help me debug and set me straight. I'm open to other solutions using LOOKUP and such, of course, but I'm pretty curious whether this approach was valid at all. I thought maybe having a TRUE/FALSE condition that writes 0 no matter the results for "Action RPG" and "Action Adventure" might have been the problem, but replacing the 0/1 with differing text responses in quotes doesn't solve it.
Thanks!
I know this is possible, I just can't get my mind around it. What I'm trying to do is concatenate any values in the List column that have a corresponding value of TRUE in the PivotTable column. Below is the table:
List
CheckBoxes
PivotTable
All
FALSE
FALSE
CORP
FALSE
FALSE
DATA
TRUE
TRUE
DP
FALSE
FALSE
DWL
FALSE
FALSE
FI
TRUE
TRUE
IC
FALSE
FALSE
IT
FALSE
FALSE
MDM
TRUE
TRUE
MFG
FALSE
FALSE
OM
FALSE
FALSE
OTC
FALSE
FALSE
PTP
FALSE
FALSE
PTPDIR
FALSE
FALSE
PTPIND
FALSE
FALSE
SCP
FALSE
FALSE
SNP
FALSE
FALSE
Titan
FALSE
FALSE
Error-Name
FALSE
FALSE
Now I was thinking this could be done with some twisted combination of MATCH, ROW, ROWS, COLUMN, COLUMNS, ADDRESS, INDIRECT, and perhaps even an OFFSET to move down to the next value. Any help would be greatly appreciated.
Hi folks,
Newbie here and I hope somebody can help!
I'll try and explain what I am trying to do as simply as possible:
Say I have 5 columns:
FirstName | LastName | intial | domain |dot |
Right, from the data in these colums I want to create some way of automating a process that would create email address's based on this data, so I would have an additional, say, 3 columns, Email1, Email2 and Email3. Each of these would contain some kind of formula that would take some of the data from the other fields and create an email address, so for example, Email1 might be:
"FirstName" + "dot" + "LastName" + "domain"
Email2 might be:
"initail" + "dot" + "LastName" + "domain"
Does this makes sense, and is it possible?
My skills with excell are limited but IT wise I can pick up most things.
Thanks in advance.
Ryan
Hi folks,
Newbie here and I hope somebody can help!
I'll try and explain what I am trying to do as simply as possible:
Say I have 5 columns:
FirstName | LastName | intial | domain |dot |
Right, from the data in these colums I want to create some way of automating a process that would create email address's based on this data, so I would have an additional, say, 3 columns, Email1, Email2 and Email3. Each of these would contain some kind of formula that would take some of the data from the other fields and create an email address, so for example, Email1 might be:
"FirstName" + "dot" + "LastName" + "domain"
Email2 might be:
"initail" + "dot" + "LastName" + "domain"
Does this makes sense, and is it possible?
My skills with excell are limited but IT wise I can pick up most things.
Thanks in advance.
Ryan
Hi,
I have a worksheet, with Column A containing some keywords and column b containing domain names, I want to be able to search a keyword in the column of domain names, and if the domain name is found, return the keyword itself in the next column.
A sample sheet is attached herewith.
Regards,
Zeeshan
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
|
|
