Data Dump and apostorphe's

Michele · External Since: May 27, 2004 Posts: 1

We have brought tons of information from PeopleSoft into
an excel spreadsheet for reporting purposes. However,
several columns (that were summed from several areas) came
over as '5425 instead of 5425. How do I go about changing
these so they can be formated to look like $5,425. I
can't seem to drop the apostrophe and I literally have 6
columns with over 2500 rows.

Thanks for your help!

Ian · External Since: Nov 19, 2003 Posts: 23

Michelle,

Answer is to open the file with M/S WORD instead of Excel.
Now use the Find/Replace function to find All the
apostrophes "'" and replace them with the "Delete Key".
Save the file.(I would suggest that if your are doing
this process regularly to record the process as a macro,
its then only a few key strokes away).
Now open the file with Excel as normal and the
apostrophes have gone.

Job done.

>-----Original Message-----
>We have brought tons of information from PeopleSoft into
>an excel spreadsheet for reporting purposes. However,
>several columns (that were summed from several areas)
came
>over as '5425 instead of 5425. How do I go about
changing
>these so they can be formated to look like $5,425. I
>can't seem to drop the apostrophe and I literally have 6
>columns with over 2500 rows.
>
>Thanks for your help!
>.
>

DanJustDan · Joined: May 19, 2006 Posts: 2

As an alternative to mucking about in Word, there's a free add-in for Excel that will allow you to make the changes within Excel.

http://www.asap-utilities.com/

Once this is in Excel it adds a bunch of utilities in a pull-down menu. All you do is select the cells with the leading apostrophe and select the "Convert text numbers to numbers" utility in the Numbers sub-menu and all the leading apostrophe's are gone.

There's also an option to convert the other way, if needed, as well as a ton of other useful utilities.

mthompso · Joined: Dec 05, 2002 Posts: 557