Search text string for ssn and copy to new column

[mary]

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

 

[Mike H]

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

 

[Ron Rosenfeld]

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

 

[Rick Rothstein \(MVP - VB\)]

I have lines of text containing various information. The SSN section

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

 

[Ron Rosenfeld]

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

 

[Rick Rothstein \(MVP - VB\)]

=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

 

[Rick Rothstein \(MVP - VB\)]

Assuming there is nothing else, numeric or text, in the string with that

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

 

[Ron Rosenfeld]

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

 

[Rick Rothstein \(MVP - VB\)]

Assuming there is nothing else, numeric or text, in the string with

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