Remove hyphens

[DCSwearingen]

We get a CSV sheet from a client with social security and account numbers that have hyphens in them. We want to strip the hyphens and then import the sheet into an AS400.

I have tried [Ctrl][H] with the appropriate column selected, with the "-" in the find field and nothing in the replace field. This works, except the leading zero gets dropped if the number has one. We need the leading zero for the numbers to match in the 400.

I have changed the column to Text prior to the find and replace, but it still drops any leading zeros.

How can I strip the hyphens without dropping a leading zero?

Using Office 2007 Plus 12.0.4518.1014 over Windows 7.

[JieJenn]

can you post a sample file?

[DCSwearingen]

Deleted all data except the field that needs the hyphen stripped.

The field labeled "Tin NUM" has leading zeros in one record and multiple hyphens in another record. These are representative of about 20,000 records.

Thanks.

[vandan_tanna]

=SUBSTITUTE(A2,"-","")

Put his in row #2 of a new column and copy it down. Format the column as "General"

[y_t]

you could also use the below to keep the leading 0s.

Please Login or Register  to view this content.

[DCSwearingen]

Both of thesse solutions worked!!

Many Thanks!