Seperating out social secuity numbers

[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

[swatsp0p]

B1 =LEFT(A1,3)
C1 =MID(A1,5,2)
D1 =RIGHT(A1,4)

HTH

Bruce

[Ron Rosenfeld]

On Wed, 21 Sep 2005 09:40:13 -0500, HCA
<[email protected]> wrote:

>
>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

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

[R. Choate]

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


"HCA" <[email protected]> wrote in message news:[email protected]...

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
------------------------------------------------------------------------
HCA's Profile: http://www.excelforum.com/member.php...o&userid=24746
View this thread: http://www.excelforum.com/showthread...hreadid=469530

[swatsp0p]

HCA: I agree with both Ron Rosenfeld and R. Choates that 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 the SS number: nnn-nn-nnnn. If the number is in a different format, my 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, Excel will recognize them as numbers. e.g. =C1*2 will return 194 (97*2)

Choose the option best suited to your needs.

Good Luck.

Bruce