How to re-format from '## to just ##?

beancounter

Board Regular
Joined
Oct 30, 2002
Messages
111
Help, I've inherited a worksheet where the previous author used a ' infront of all the numbers. I can't seem to change the format and use the numbers as numbers they can only be used as text. Any suggestions? Thanks
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

RichardS

Well-known Member
Joined
Feb 16, 2002
Messages
761
Hi fellow beancounter, welcome to the Board

Enter 1 in a spare cell, copy, select "text" numbers, paste|special, check the multiply radio button.

Richard

_________________
constrMining.gif

This message was edited by RichardS on 2002-10-31 19:46
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
put a zero in a spare cell, select & copy it, select the range of 'text numbers', go to edit | paste special, select add, hit OK.

paddy
 

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
WELCOME TO THE BOARD!

Here is one way to do it. You could create a new worksheet and in cell A1 put the following:

=value(Sheet1!A1)

This would convert the value of cell A1 on Sheet1 to a number. Copy the formula and paste it throughout the new worksheet to the same size as the original worksheet.

Another way to do it would be to copy the area of the original that you want to convert. Then Paste Special.....VALUES.

Take your pick.
 

beancounter

Board Regular
Joined
Oct 30, 2002
Messages
111
Thanks Phantom1975 your second suggestion worked. The other paste special way didn't work and I tried them all.

Thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,550
Members
414,316
Latest member
ExcelLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top