Converting data in a cell.

[kollur]

Hi there,
In the attached sheet I have an account # in a cell that is as follows:

625112-01-2-000-0506
I need to remove the hyphens and have a single # in the cell. I have tried converting it using "Text to columns" and then adding them together ( Using & or concatenate) but I'm running into a problem. As soon as I convert I lost the "0"s. For example:
The second column become 1 instead of 01
the 4th column loses the multiple 0s and returns a singe 0
the 5th column return only 506.

Could some one help me please? Thanks a bunch for looking into it!

Thanks,
SJ

[Tony Valko]

I didn't download your file.

See if this works for you...

A1 = 625112-01-2-000-0506

This formula in B1:

=SUBSTITUTE(A1,"-","")

That will return 6251120120000506 as a TEXT value.

[WouterM]

Hi,

You can try in a free cell:

Please Login or Register  to view this content.

Click on Add Reputation below please is this helps.

[kollur]

Thanks Sir.
It worked!

[Tony Valko]

You're welcome. Thanks for the feedback!