Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search text string for ssn and copy to new column
I have lines of text containing various information. The SSN section appears
at different spots in the string but is always in the same format xxx-xx-xxxx. Can I use a formula (IF statement?) to search the strings to find the ssn and copy it into a separate column? The spacing between items, characters per item and string length always vary. Ex below. ADAM SMITH B xxx-xx-xxxx 04/2C 06/26/2006 25951 N 5988 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search text string for ssn and copy to new column
Mary,
This works for your displayed example. It searches for the first "-" in the string, counts back 3 places and returns 11 characters from that. If there are other "-" minus signs before your SSN then this doesn't work:- =MID(A1,SEARCH("-",A1,1)-3,11) Mike "mary" wrote: I have lines of text containing various information. The SSN section appears at different spots in the string but is always in the same format xxx-xx-xxxx. Can I use a formula (IF statement?) to search the strings to find the ssn and copy it into a separate column? The spacing between items, characters per item and string length always vary. Ex below. ADAM SMITH B xxx-xx-xxxx 04/2C 06/26/2006 25951 N 5988 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search text string for ssn and copy to new column
On Thu, 27 Dec 2007 12:38:01 -0800, mary
wrote: I have lines of text containing various information. The SSN section appears at different spots in the string but is always in the same format xxx-xx-xxxx. Can I use a formula (IF statement?) to search the strings to find the ssn and copy it into a separate column? The spacing between items, characters per item and string length always vary. Ex below. ADAM SMITH B xxx-xx-xxxx 04/2C 06/26/2006 25951 N 5988 Assuming there is nothing else, numeric or text, in the string with that format, you could use the formula: =MID(A1,SEARCH("???-??-????",A1),11) --ron |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search text string for ssn and copy to new column
I have lines of text containing various information. The SSN section
appears at different spots in the string but is always in the same format xxx-xx-xxxx. Can I use a formula (IF statement?) to search the strings to find the ssn and copy it into a separate column? The spacing between items, characters per item and string length always vary. Ex below. ADAM SMITH B xxx-xx-xxxx 04/2C 06/26/2006 25951 N 5988 Assuming there is nothing else, numeric or text, in the string with that format, you could use the formula: =MID(A1,SEARCH("???-??-????",A1),11) To prevent the #VALUE! error (with having to devise a complicated IF test), you modify your formula like this... =MID(A1,SEARCH("???-??-????",A1&" - - "),11) Rick |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search text string for ssn and copy to new column
On Thu, 27 Dec 2007 16:01:09 -0500, "Rick Rothstein \(MVP - VB\)"
wrote: I have lines of text containing various information. The SSN section appears at different spots in the string but is always in the same format xxx-xx-xxxx. Can I use a formula (IF statement?) to search the strings to find the ssn and copy it into a separate column? The spacing between items, characters per item and string length always vary. Ex below. ADAM SMITH B xxx-xx-xxxx 04/2C 06/26/2006 25951 N 5988 Assuming there is nothing else, numeric or text, in the string with that format, you could use the formula: =MID(A1,SEARCH("???-??-????",A1),11) To prevent the #VALUE! error (with having to devise a complicated IF test), you modify your formula like this... =MID(A1,SEARCH("???-??-????",A1&" - - "),11) Rick Thanks for the addition. I usually let the requestor decide what he wants if the substring is not present in the searched string. --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search text string for ssn and copy to new column
=MID(A1,SEARCH("???-??-????",A1),11)
To prevent the #VALUE! error (with having to devise a complicated IF test), you modify your formula like this... Sigh! Learn to type Rick!!! The first word in the parentheses was supposed to have been "without". Rick =MID(A1,SEARCH("???-??-????",A1&" - - "),11) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search text string for ssn and copy to new column
Assuming there is nothing else, numeric or text, in the string with that
format, you could use the formula: =MID(A1,SEARCH("???-??-????",A1),11) To prevent the #VALUE! error (with having to devise a complicated IF test), you modify your formula like this... =MID(A1,SEARCH("???-??-????",A1&" - - "),11) Rick Thanks for the addition. I usually let the requestor decide what he wants if the substring is not present in the searched string. Normally, I would agree with that approach; but, unless I am missing something obvious (always a distinct possibility), any such test would be somewhat longish and/or add a duplicate calling of the result formula, so I figured I would offer (mainly for the archives), the "give the function something to find" method of getting around that situation. Rick |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search text string for ssn and copy to new column
On Thu, 27 Dec 2007 21:28:10 -0500, "Rick Rothstein \(MVP - VB\)"
wrote: Assuming there is nothing else, numeric or text, in the string with that format, you could use the formula: =MID(A1,SEARCH("???-??-????",A1),11) To prevent the #VALUE! error (with having to devise a complicated IF test), you modify your formula like this... =MID(A1,SEARCH("???-??-????",A1&" - - "),11) Rick Thanks for the addition. I usually let the requestor decide what he wants if the substring is not present in the searched string. Normally, I would agree with that approach; but, unless I am missing something obvious (always a distinct possibility), any such test would be somewhat longish and/or add a duplicate calling of the result formula, so I figured I would offer (mainly for the archives), the "give the function something to find" method of getting around that situation. Rick One approach, which I tend to use myself, is to use conditional formatting. I can either "white-out" the result, or use formatting of interior and font to make the error really stand out. The cell formula stays the same. --ron |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search text string for ssn and copy to new column
Assuming there is nothing else, numeric or text, in the string with
that format, you could use the formula: =MID(A1,SEARCH("???-??-????",A1),11) To prevent the #VALUE! error (with having to devise a complicated IF test), you modify your formula like this... =MID(A1,SEARCH("???-??-????",A1&" - - "),11) Thanks for the addition. I usually let the requestor decide what he wants if the substring is not present in the searched string. Normally, I would agree with that approach; but, unless I am missing something obvious (always a distinct possibility), any such test would be somewhat longish and/or add a duplicate calling of the result formula, so I figured I would offer (mainly for the archives), the "give the function something to find" method of getting around that situation. One approach, which I tend to use myself, is to use conditional formatting. I can either "white-out" the result, or use formatting of interior and font to make the error really stand out. The cell formula stays the same. That certainly is a nice, workable way to get out of carrying a longish logical expression for an IF function to evaluate in every cell the formula is copied to... and is especially usable for formulas whose constructions do not lend themselves to the "give the function something to find" method. I guess the only downside, if you want to call it that, is when modifying such a formula in a cell, you have to remember to change the conditional formula as well; but, if this is a procedure you tend to use a lot, I'm guessing that is not such a big deal to remember to do. Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text search within a string using formula | Excel Worksheet Functions | |||
Search, find or lookup defined text in text string | Excel Worksheet Functions | |||
search for date in long string of text | Excel Discussion (Misc queries) | |||
How do I do a multiple search using key words in a text string | Excel Discussion (Misc queries) | |||
Newbie: How to search a text string from right | Excel Worksheet Functions |