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

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

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

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

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

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)

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

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

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