Archive of Mr Excel Message Board

Back to Edit in Excel archive index
Back to archive home

Formatting

Posted by George on November 14, 2001 2:26 AM

Hi,
I have a large spreadsheet with a huge table of numbers in it.

The trouble is, each number is written in the following format:

#'##0

..so 2345 looks like 2'345 in the cell - it is, in fact text.

How can I reformate these numbers from this text format into a number format without having to remove the " ' " from each number so that 2'345 reads 2345, so it can be used in calculations??

Thanks,
George.

Re: Formatting

Posted by Dank on November 14, 2001 3:25 AM

Hello,

How about selecting the range in question and choosing Edit, Replace and replace the apostrophe with nothing.

Regards,
Dan.

Re: Formatting

Posted by Hodge on November 14, 2001 5:12 AM

You can do this with a custom number format.

- Select you cells
- Format Cells
- Number Tab
- Custom
- Enter #'##0 into the "Type" field

That should do it.

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.