Copying a Number from one cell to another in a different format

[Rodster1966]

Hi Excel Gurus,

Can I please have your assistance.

I have a column of numbers 'A' that I need to copy to Column 'B' but they need to come across in a different format as follows:


Column A

000200501410


Column B

2-501410


Column A

001000499489


Column B

1-499489


In both of the above cases I need to delete or omit the first 2 or three 'Zeros' with the '-' Dash after the first digit.

Could you please show me how this can be achieved.

This is quite urgent so your assistance would be greatly appreciated.

Kind Regards

Rodney

 

[steve the fish]

Is it always the first non zero number, a dash then the last 6 numbers?

 

[steve the fish]

If it isnt this should cover it anyhow:

=LEFT(0+A1,1)&"-"&0+RIGHT(A1,LEN(0+A1)-1)

 

[Rodster1966]

Sorry Steve I have only just seen your reply and many thanks.

The number will invariably start with the following sequence:

eg. 1000508648

My Accounting Program only accepts 8 digits, so I need to convert the above number to:

1-508648

Another eg can be: 1200505106

This will need to be converted to:

12505106 (8 digits)

Many thanks

Rodney

 

[steve the fish]

How about giving more examples that are fully representative of the data with expected results as it seems the goalposts have moved.

 

[Rick Rothstein]

I agree with Steve that more, varied examples would be useful. However, just guessing from what you have posted so far...

=LEFT(SUBSTITUTE(REPLACE(A1,LEN(A1)-5,6,""),0,"")&"-",2)&TEXT(RIGHT(A1,6),"000000")

 

[Peter_SSs]

This will need to be converted to:

12505106

All other example results have included a "-". What is different about this one that does not have a "-" in the result?

 

[Rodster1966]

My apologies for the late reply but I just want to thank both of you, Steve and Rick, I have tested both formulas and both do the job fine so many thanks, I truly appreciate your assistance ).


Kind Regards

Rod