loosing test fomat after using replace command

R

Richard Miller

we are importing payroll information which includes a SS number formatted
with the dashes (xxx-xx-xxxx). The infomation is used to upload to the state
which requires the data to be in CSV format with no dashes in the SS number.
If we format the SS column as TEXT and apply the replace all to the column
reqursting that (-) be replaced with ( ), the dashes are sucessfully removed,
but the fromat seems to revert to general and any SS number that has a
leading 0 has the zero dropped.

Any suggestion as to how to now loose the leading zero thru the process?
 
D

Dave Peterson

If you format the resulting cell as: 000000000
You'll see your leading 0's.

And if you save as a .csv, then open that .csv in Notepad, you'll be able to see
how it worked.

Or...

Use a helper column of formulas:
=substitute(a1,"-","")
And drag down.

This will return a string.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top