text to number.... 2

[Yogi39]

Working with a worksheet which has numbers preceded by '.
Ex: cell content = '123
which makes the number seen as text...how can I remove the ' to get all my data as numbers ?

 

[carrr]

Highlight desired area.
Edit > Replace > put a ' in the Replace field, leave the replace with field blank, hit replace all

"'Tis an ill wind that blows no minds." - Malaclypse the Younger

 

[carrr]

Scratch that...jumped the gun...ineffective

"'Tis an ill wind that blows no minds." - Malaclypse the Younger

 

[dyarwood]

I've just tried '123 myself and the number 123 automatically appears. Check the formatting of the cells and if that doesn't tell you much do a find and replace to find ' and replace it with nothing

dyarwood

 

[carrr]

Find and replace won't work, I learned quickly.
Copy all cells with the ' in them and then paste special into another column, checking the Values option before you paste. This will eliminate your ' marks.

"'Tis an ill wind that blows no minds." - Malaclypse the Younger

 

[klmorgan]

You could use =Value function to convert text into values in a spare column then copy the column and Paste\special\values it bck into the original column

Regards

Keith

 

[TonyJollans]

Hi Yogi39,

Find an empty cell somewhere and copy it.
Select the cells with your 'text numbers' and ..
Edit > PasteSpecial > Add

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[xlbo]

If this is what I think it is, you'll all kick yourselves

My bet is that under Tools>Options>Transition
You have Transition Navigation Keys ticked

this causes excel to show a ' in front of a number to indicate it is stored as text

To get rid of the ' just untick the box. The number will still be stored as text though

If you don't want to untick Transition Navigation Keys you need to use the *1 fix

Enter a 1 into any blank cell
Copy It
Select all the data that needs the ' removed
Edit>Pastespecial Check Values and Tick Multiply

et voila, no more 's as the data will be transformed to numbers rather than text

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Yogi39]

Thanks the Edit > PasteSpecial > Add worked great !