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
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 |
#7
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 |
#8
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) |
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 |