Using ' in front of numbers

[jmmarsh]

I have been given a spreadsheet full of employee IDs - someone placed a ' in front of all the IDs however and management wants that removed (I'm thinking someone did it so zeros would show) - is there a mass way to remove the ' from all IDs and have zeros always show also? Thx : ) I did try the find/replace but it doesn't find the ' when searching.

 

[Joe4]

Highlight the column, format as a Numeric option, then go to Data | Text To Columns and click Finish. You just need to do one column at a time.

 

[Oaktree]

Put a 0 in any unused cell. Copy that cell, select the range with the IDs, and use edit --> paste special --> add.

 

[jmmarsh]

YAH thx

THANKS!!!!!!

 

[Grizlore]

Highlight the entire column
Click EDIT --> REPLACE
Enter the ' in the "find what" box
Leave the "replace with" empty
Click "REPLACE ALL"


Think I've just posted my first solution to someone elses problem!!!

 

[jmmarsh]

yah - I tried that but Excel doesn't recognize the ' as something to replace when using find/replace.

I did find that if you copy the column and do paste special and paste as values that works too : D

 

[Legacy 51064]

You can save the page as a text file, then reopen it in excel.