Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find String in another string - only between spaces
Hi,
I need to find and return a string in another string only if the searched string is straight A to Z chars in a row. saying A1:A3 is the string to find in B1:B3 is the formula. need to find string "AB" A1: ABDF_ABD A2: ABDF_AB A3: ABDF AB result should be B1 - NA, B2-AB , B3-AB |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find String in another string - only between spaces
Does this help?
B1, copied down: =IF(ISNUMBER(FIND(" AB "," "&SUBSTITUTE(A1,"_"," ")&" ")),"AB","NA") In article , Nir wrote: Hi, I need to find and return a string in another string only if the searched string is straight A to Z chars in a row. saying A1:A3 is the string to find in B1:B3 is the formula. need to find string "AB" A1: ABDF_ABD A2: ABDF_AB A3: ABDF AB result should be B1 - NA, B2-AB , B3-AB |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find String in another string - only between spaces
LOL - I'm sure that probably made perfect sense to yuou when you wrote it,
but I'm just sat here scratching my head to be honest. I don't see any of the strings containg AB BETWEEN spaces as per your subject line. Also, just explain that A-Z characaters ina row for us as well please, just so we are 100% on what you are after. I had thought you were after for example, all instances where AB is in the string and between spaces, but your example would seem to discount that thought. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------- It's easier to beg forgiveness than ask permission :-) "Nir" wrote: Hi, I need to find and return a string in another string only if the searched string is straight A to Z chars in a row. saying A1:A3 is the string to find in B1:B3 is the formula. need to find string "AB" A1: ABDF_ABD A2: ABDF_AB A3: ABDF AB result should be B1 - NA, B2-AB , B3-AB |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find String in another string - only between spaces
Ken,
I will extend my question. the idea is to find a string in other string only if it is a "perfect" word. "perfect" word as it is idetentified in the file can be positioned in the beginning or end of the string or between spaces or signs. can you assist with? TIA Nir "Ken Wright" wrote: LOL - I'm sure that probably made perfect sense to yuou when you wrote it, but I'm just sat here scratching my head to be honest. I don't see any of the strings containg AB BETWEEN spaces as per your subject line. Also, just explain that A-Z characaters ina row for us as well please, just so we are 100% on what you are after. I had thought you were after for example, all instances where AB is in the string and between spaces, but your example would seem to discount that thought. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------- It's easier to beg forgiveness than ask permission :-) "Nir" wrote: Hi, I need to find and return a string in another string only if the searched string is straight A to Z chars in a row. saying A1:A3 is the string to find in B1:B3 is the formula. need to find string "AB" A1: ABDF_ABD A2: ABDF_AB A3: ABDF AB result should be B1 - NA, B2-AB , B3-AB |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find String in another string - only between spaces
Have you tried the solution I offered?
In article , Nir wrote: Ken, I will extend my question. the idea is to find a string in other string only if it is a "perfect" word. "perfect" word as it is idetentified in the file can be positioned in the beginning or end of the string or between spaces or signs. can you assist with? TIA Nir |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find String in another string - only between spaces
On Wed, 01 Nov 2006 17:03:21 -0500, Domenic wrote:
Have you tried the solution I offered? It should work if the only separators are <space and <underscore. --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find String in another string - only between spaces
hi Nir,
So what you want is to be able to find a test string: a: comprising the whole of the target string, b: followed by a space at the start of the target string, c: following a space at the end of the target string, d: with spaces both sides anywhere within the target string. Correct? Cheers -- macropod [MVP - Microsoft Word] "Nir" wrote in message ... Ken, I will extend my question. the idea is to find a string in other string only if it is a "perfect" word. "perfect" word as it is idetentified in the file can be positioned in the beginning or end of the string or between spaces or signs. can you assist with? TIA Nir "Ken Wright" wrote: LOL - I'm sure that probably made perfect sense to yuou when you wrote it, but I'm just sat here scratching my head to be honest. I don't see any of the strings containg AB BETWEEN spaces as per your subject line. Also, just explain that A-Z characaters ina row for us as well please, just so we are 100% on what you are after. I had thought you were after for example, all instances where AB is in the string and between spaces, but your example would seem to discount that thought. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------- It's easier to beg forgiveness than ask permission :-) "Nir" wrote: Hi, I need to find and return a string in another string only if the searched string is straight A to Z chars in a row. saying A1:A3 is the string to find in B1:B3 is the formula. need to find string "AB" A1: ABDF_ABD A2: ABDF_AB A3: ABDF AB result should be B1 - NA, B2-AB , B3-AB |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find String in another string - only between spaces
Nir wrote...
.... the idea is to find a string in other string only if it is a "perfect" word. "perfect" word as it is idetentified in the file can be positioned in the beginning or end of the string or between spaces or signs. Still unclear. Your original examples. "Nir" wrote: .... need to find string "AB" A1: ABDF_ABD A2: ABDF_AB A3: ABDF AB result should be B1 - NA, B2-AB , B3-AB Do you mean you need to find the substring AB as long as the characters on either side of it, if any, aren't letters? Or aren't letters or numerals? First step would be finding all instances of the substring sought (ss) in the string (s), then checking whether the characters immediately before and after the substring sought are letters or other 'word' characters. Easier with some common defined names like seq =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,1024,1)) bc ="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuv wxyz" Then try the array formula =IF(COUNT(IF(MID(s,seq,LEN(ss))=ss,1/ISERROR(FIND(MID(" "&s,seq,1),bc)) /ISERROR(FIND(MID(s&" ",seq+LEN(ss),1),bc)))),ss,"NA") to search for ss in s, and if found return ss if it's bracketed by characters not in bc. Otherwise, return NA. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find String in another string - only between spaces
Ah yes, thanks Ron! I missed the part about "being positioned between
signs", whatever that means... :) In article , Ron Rosenfeld wrote: On Wed, 01 Nov 2006 17:03:21 -0500, Domenic wrote: Have you tried the solution I offered? It should work if the only separators are <space and <underscore. --ron |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find String in another string - only between spaces
On Wed, 01 Nov 2006 22:03:38 -0500, Domenic wrote:
Ah yes, thanks Ron! I missed the part about "being positioned between signs", whatever that means... :) Well, I, too, have found the OP's description unclear. So I've just been lurking :-) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find string within other string | Excel Worksheet Functions | |||
Insert spaces in text string in Excel | Excel Discussion (Misc queries) | |||
Insert spaces in String of Numbers | Excel Worksheet Functions | |||
List File Properties - Author | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |