I am pulling SSN out of one of my systems and putting it in excel.
When it comes out of my AS/400 it does not have a leading zero if
needed. I need a have a formula that will add a zero to the begging
of a 8 character string if needed but if is in already 9 characters
long I need to leave it alone.

A1 has the SSN in text, which is how I need it. I need A2 to have the
SSN with the leading zero if not 9 characters long.

=REPT("0",9-LEN(A21))&A21

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
oups.com...
I am pulling SSN out of one of my systems and putting it in excel.
When it comes out of my AS/400 it does not have a leading zero if
needed. I need a have a formula that will add a zero to the begging
of a 8 character string if needed but if is in already 9 characters
long I need to leave it alone.

A1 has the SSN in text, which is how I need it. I need A2 to have the
SSN with the leading zero if not 9 characters long.

Apply custom format on cell A1, enter format code 00000000 (eight 0 digits)

Regards,
Stefi

€� ezt Ã*rta:

I am pulling SSN out of one of my systems and putting it in excel.
When it comes out of my AS/400 it does not have a leading zero if
needed. I need a have a formula that will add a zero to the begging
of a 8 character string if needed but if is in already 9 characters
long I need to leave it alone.

A1 has the SSN in text, which is how I need it. I need A2 to have the
SSN with the leading zero if not 9 characters long.

Apply custom format on cell A1: enter format code 00000000 (eight 0 digit)

Regards,
Stefi

€� ezt Ã*rta:

I am pulling SSN out of one of my systems and putting it in excel.
When it comes out of my AS/400 it does not have a leading zero if
needed. I need a have a formula that will add a zero to the begging
of a 8 character string if needed but if is in already 9 characters
long I need to leave it alone.

A1 has the SSN in text, which is how I need it. I need A2 to have the
SSN with the leading zero if not 9 characters long.

I bet you meant nine 0's.

Or even Format|cells|number tab|special category|social security number
(to get 000-00-0000)

Stefi wrote:

Apply custom format on cell A1, enter format code 00000000 (eight 0 digits)

Regards,
Stefi

€� ezt Ã*rta:

I am pulling SSN out of one of my systems and putting it in excel.
When it comes out of my AS/400 it does not have a leading zero if
needed. I need a have a formula that will add a zero to the begging
of a 8 character string if needed but if is in already 9 characters
long I need to leave it alone.

A1 has the SSN in text, which is how I need it. I need A2 to have the
SSN with the leading zero if not 9 characters long.



--

Dave Peterson

Hi Dave,

No, I meant eight 0's, because cflarida wanted leading zeros up to 8
character length. In case of nine 0's string e.g. 1234567 would be displayed
as 001234567 and it's 9 character length instead of 8 character long
01234567. Did I misunderstand something?

Regards,
Stefi


€žDave Peterson€� ezt Ã*rta:

I bet you meant nine 0's.

Or even Format|cells|number tab|special category|social security number
(to get 000-00-0000)

Stefi wrote:

Apply custom format on cell A1, enter format code 00000000 (eight 0 digits)

Regards,
Stefi

€Â� ezt ÃÂ*rta:

I am pulling SSN out of one of my systems and putting it in excel.
When it comes out of my AS/400 it does not have a leading zero if
needed. I need a have a formula that will add a zero to the begging
of a 8 character string if needed but if is in already 9 characters
long I need to leave it alone.

A1 has the SSN in text, which is how I need it. I need A2 to have the
SSN with the leading zero if not 9 characters long.



--

Dave Peterson

I think so--but I've been wrong before.

"I need a have a formula that will add a zero to the begging (sic) of a 8
character string."


Stefi wrote:

Hi Dave,

No, I meant eight 0's, because cflarida wanted leading zeros up to 8
character length. In case of nine 0's string e.g. 1234567 would be displayed
as 001234567 and it's 9 character length instead of 8 character long
01234567. Did I misunderstand something?

Regards,
Stefi

€žDave Peterson€� ezt Ã*rta:

I bet you meant nine 0's.

Or even Format|cells|number tab|special category|social security number
(to get 000-00-0000)

Stefi wrote:

Apply custom format on cell A1, enter format code 00000000 (eight 0 digits)

Regards,
Stefi

€Â� ezt ÃÂ*rta:

I am pulling SSN out of one of my systems and putting it in excel.
When it comes out of my AS/400 it does not have a leading zero if
needed. I need a have a formula that will add a zero to the begging
of a 8 character string if needed but if is in already 9 characters
long I need to leave it alone.

A1 has the SSN in text, which is how I need it. I need A2 to have the
SSN with the leading zero if not 9 characters long.



--

Dave Peterson


--

Dave Peterson

Now I see, I haven't notice that the request was simply to display a SSN
formatted string. Not speaking English as mother tongue abbreviation SSN
didn't immediately bring to my mind its full meaning.
Stefi



€žDave Peterson€� ezt Ã*rta:

I think so--but I've been wrong before.

"I need a have a formula that will add a zero to the begging (sic) of a 8
character string."


Stefi wrote:

Hi Dave,

No, I meant eight 0's, because cflarida wanted leading zeros up to 8
character length. In case of nine 0's string e.g. 1234567 would be displayed
as 001234567 and it's 9 character length instead of 8 character long
01234567. Did I misunderstand something?

Regards,
Stefi

€žDave Peterson€Â� ezt ÃÂ*rta:

I bet you meant nine 0's.

Or even Format|cells|number tab|special category|social security number
(to get 000-00-0000)

Stefi wrote:

Apply custom format on cell A1, enter format code 00000000 (eight 0 digits)

Regards,
Stefi

€ÂÂ� ezt ÃÂÂ*rta:

I am pulling SSN out of one of my systems and putting it in excel.
When it comes out of my AS/400 it does not have a leading zero if
needed. I need a have a formula that will add a zero to the begging
of a 8 character string if needed but if is in already 9 characters
long I need to leave it alone.

A1 has the SSN in text, which is how I need it. I need A2 to have the
SSN with the leading zero if not 9 characters long.



--

Dave Peterson


--

Dave Peterson

Well, your writing in English is very good.



Stefi wrote:

Now I see, I haven't notice that the request was simply to display a SSN
formatted string. Not speaking English as mother tongue abbreviation SSN
didn't immediately bring to my mind its full meaning.
Stefi

€žDave Peterson€� ezt Ã*rta:

I think so--but I've been wrong before.

"I need a have a formula that will add a zero to the begging (sic) of a 8
character string."


Stefi wrote:

Hi Dave,

No, I meant eight 0's, because cflarida wanted leading zeros up to 8
character length. In case of nine 0's string e.g. 1234567 would be displayed
as 001234567 and it's 9 character length instead of 8 character long
01234567. Did I misunderstand something?

Regards,
Stefi

€žDave Peterson€Â� ezt ÃÂ*rta:

I bet you meant nine 0's.

Or even Format|cells|number tab|special category|social security number
(to get 000-00-0000)

Stefi wrote:

Apply custom format on cell A1, enter format code 00000000 (eight 0 digits)

Regards,
Stefi

€ÂÂ� ezt ÃÂÂ*rta:

I am pulling SSN out of one of my systems and putting it in excel.
When it comes out of my AS/400 it does not have a leading zero if
needed. I need a have a formula that will add a zero to the begging
of a 8 character string if needed but if is in already 9 characters
long I need to leave it alone.

A1 has the SSN in text, which is how I need it. I need A2 to have the
SSN with the leading zero if not 9 characters long.



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

On Feb 28, 11:36Â*am, Dave Peterson wrote:
Well, your writing in English is very good.





Stefi wrote:

Now I see, I haven't notice that the request was simply to display a SSN
formatted string. Not speaking English as mother tongue abbreviation SSN
didn't immediately bring to my mind its full meaning.
Stefi

€žDave Peterson€� ezt Ã*rta:

I think so--but I've been wrong before.

"I need a have a formula that will add a zero to the begging (sic) of a 8
character string."

Stefi wrote:

Hi Dave,

No, I meant eight 0's, because cflarida wanted leading zeros up to 8
character length. In case of nine 0's string e.g. 1234567 would be displayed
as 001234567 and it's 9 character length instead of 8 character long
01234567. Did I misunderstand something?

Regards,
Stefi

€žDave Peterson€Â� ezt ÃÂ*rta:

I bet you meant nine 0's.

Or even Format|cells|number tab|special category|social security number
(to get 000-00-0000)

Stefi wrote:

Apply custom format on cell A1, enter format code 00000000 (eight 0 digits)

Regards,
Stefi

€ÂÂ� ezt ÃÂÂ*rta:

I am pulling SSN out of one of my systems and putting it in excel.
When it comes out of my AS/400 it does not have a leading zero if
needed. Â*I need a have a formula that will add a zero to the begging
of a 8 character string if needed but if is in already 9 characters
long I need to leave it alone.

A1 has the SSN in text, which is how I need it. I need A2 to have the
SSN with the leading zero if not 9 characters long.

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson- Hide quoted text -

- Show quoted text -

I tried that ad that works. I also got the following to work.
=TEXT(MOD(INT(A2/1),1000000000),"000000000")

If I have the -'s in how would I take them out? 000-00-0000 would
like it to return with 000000000

=substitute(a1,"-","")
will return a text string
=--substitute(a1,"-","")
will return a number


wrote:

On Feb 28, 11:36Â am, Dave Peterson wrote:
Well, your writing in English is very good.





Stefi wrote:

Now I see, I haven't notice that the request was simply to display a SSN
formatted string. Not speaking English as mother tongue abbreviation SSN
didn't immediately bring to my mind its full meaning.
Stefi

€žDave Peterson€� ezt Ã*rta:

I think so--but I've been wrong before.

"I need a have a formula that will add a zero to the begging (sic) of a 8
character string."

Stefi wrote:

Hi Dave,

No, I meant eight 0's, because cflarida wanted leading zeros up to 8
character length. In case of nine 0's string e.g. 1234567 would be displayed
as 001234567 and it's 9 character length instead of 8 character long
01234567. Did I misunderstand something?

Regards,
Stefi

€žDave Peterson€Â� ezt ÃÂ*rta:

I bet you meant nine 0's.

Or even Format|cells|number tab|special category|social security number
(to get 000-00-0000)

Stefi wrote:

Apply custom format on cell A1, enter format code 00000000 (eight 0 digits)

Regards,
Stefi

€ÂÂ� ezt ÃÂÂ*rta:

I am pulling SSN out of one of my systems and putting it in excel.
When it comes out of my AS/400 it does not have a leading zero if
needed. Â I need a have a formula that will add a zero to the begging
of a 8 character string if needed but if is in already 9 characters
long I need to leave it alone.

A1 has the SSN in text, which is how I need it. I need A2 to have the
SSN with the leading zero if not 9 characters long.

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson- Hide quoted text -

- Show quoted text -

I tried that ad that works. I also got the following to work.
=TEXT(MOD(INT(A2/1),1000000000),"000000000")

If I have the -'s in how would I take them out? 000-00-0000 would
like it to return with 000000000

--

Dave Peterson

Thanks, Dave, that's really kind of you taking the trouble of appreciating my
English.
Stefi


€žDave Peterson€� ezt Ã*rta:

Well, your writing in English is very good.



Stefi wrote:

Now I see, I haven't notice that the request was simply to display a SSN
formatted string. Not speaking English as mother tongue abbreviation SSN
didn't immediately bring to my mind its full meaning.
Stefi

€žDave Peterson€Â� ezt ÃÂ*rta:

I think so--but I've been wrong before.

"I need a have a formula that will add a zero to the begging (sic) of a 8
character string."


Stefi wrote:

Hi Dave,

No, I meant eight 0's, because cflarida wanted leading zeros up to 8
character length. In case of nine 0's string e.g. 1234567 would be displayed
as 001234567 and it's 9 character length instead of 8 character long
01234567. Did I misunderstand something?

Regards,
Stefi

€žDave Peterson€ÂÂ� ezt ÃÂÂ*rta:

I bet you meant nine 0's.

Or even Format|cells|number tab|special category|social security number
(to get 000-00-0000)

Stefi wrote:

Apply custom format on cell A1, enter format code 00000000 (eight 0 digits)

Regards,
Stefi

€ÀšÃ‚Â� ezt ÃÆÀšÃ‚Â*rta:

I am pulling SSN out of one of my systems and putting it in excel.
When it comes out of my AS/400 it does not have a leading zero if
needed. I need a have a formula that will add a zero to the begging
of a 8 character string if needed but if is in already 9 characters
long I need to leave it alone.

A1 has the SSN in text, which is how I need it. I need A2 to have the
SSN with the leading zero if not 9 characters long.



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I enjoy reading the technical side of your posts, too <bg.

Stefi wrote:

Thanks, Dave, that's really kind of you taking the trouble of appreciating my
English.
Stefi

€žDave Peterson€� ezt Ã*rta:

Well, your writing in English is very good.



Stefi wrote:

Now I see, I haven't notice that the request was simply to display a SSN
formatted string. Not speaking English as mother tongue abbreviation SSN
didn't immediately bring to my mind its full meaning.
Stefi

€žDave Peterson€Â� ezt ÃÂ*rta:

I think so--but I've been wrong before.

"I need a have a formula that will add a zero to the begging (sic) of a 8
character string."


Stefi wrote:

Hi Dave,

No, I meant eight 0's, because cflarida wanted leading zeros up to 8
character length. In case of nine 0's string e.g. 1234567 would be displayed
as 001234567 and it's 9 character length instead of 8 character long
01234567. Did I misunderstand something?

Regards,
Stefi

€žDave Peterson€ÂÂ� ezt ÃÂÂ*rta:

I bet you meant nine 0's.

Or even Format|cells|number tab|special category|social security number
(to get 000-00-0000)

Stefi wrote:

Apply custom format on cell A1, enter format code 00000000 (eight 0 digits)

Regards,
Stefi

€ÀšÃ‚Â� ezt ÃÆÀšÃ‚Â*rta:

I am pulling SSN out of one of my systems and putting it in excel.
When it comes out of my AS/400 it does not have a leading zero if
needed. I need a have a formula that will add a zero to the begging
of a 8 character string if needed but if is in already 9 characters
long I need to leave it alone.

A1 has the SSN in text, which is how I need it. I need A2 to have the
SSN with the leading zero if not 9 characters long.



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson