Seperating out social secuity numbers

  • Thread starter Thread starter HCA
  • Start date Start date
H

HCA

Is there a formula that enables you to seperate out a string a numbers
in a cell to three different cells.
like a social security number.

For example:

Starting with
675-97-7876 in A1

Conclusion
675 in B1
97 in C1
7876 in D1

Thanks in advance
 
Is there a formula that enables you to seperate out a string a numbers
in a cell to three different cells.
like a social security number.

For example:

Starting with
675-97-7876 in A1

Conclusion
675 in B1
97 in C1
7876 in D1

Thanks in advance
Click to expand...

It depends on whether the SSN is a formatted number, or a text string.

If it is a text string, then using the Data/Text to Columns wizard with
<hyphen> as the delimiter is probably the simplest.

One could also use text formulas:
B1: =LEFT(A1,3)
C1: =MID(A1,5,2)
D1: =RIGHT(A1,4)

If it is a formatted number, then:
B1: =LEFT(TEXT(A1,"000-00-0000"),3)
C1: =MID(TEXT(A1,"000-00-0000"),5,2)
D1: =RIGHT(TEXT(A1,"000-00-0000"),4)

--------------------------

The above formulas will return text strings. If you need them to be numeric,
then precede each formula with a double unary; e.g. =--LEFT(A1,3)

----------------------------------
Also, if the SSN is a number, then:

B1: =INT(A1/10^6)
C1: =MOD(INT(A1/10^4),100)
D1: =MOD(A1,10^4)

will return numbers.


--ron
 
Have you tried the text-to-columns feature? It is under the Data menu. You just click "text-to-columns" and then choose "delimited"
in the dialog. Also, when you tell it what delimiter you want, uncheck the default (Tabs) and check "Other", then put a hyphen in
the little box next to "Other". Then click Finish and you're done. You can spread out a whole column of SSNs this way, exactly as
you said you wanted it done. Give it a try.
--
RMC,CPA



Is there a formula that enables you to seperate out a string a numbers
in a cell to three different cells.
like a social security number.

For example:

Starting with
675-97-7876 in A1

Conclusion
675 in B1
97 in C1
7876 in D1

Thanks in advance
 
HCA: I agree with both Ron Rosenfeld and R. Choates tha
Text-to-Columns is the easiest way to accomplish your task.

You asked for a formula and I, without considering other methods
responded with a formula that met your description of the form of th
SS number: nnn-nn-nnnn. If the number is in a different format, m
formula won't work.

Note: Using my formula, the results -are displayed - as text. However
if you have a formula that uses the results in a numeric equation, Exce
-will- recognize them as numbers. e.g. =C1*2 will return 194 (97*2)

Choose the option best suited to your needs.

Good Luck.

Bruc
 
Back
Top