from text to number

[CCNProjects]

Hi.

I have cells that contain 5+3 instead of 8, for example.
(2+3, 5+0, 6+4, 1+7...)
What is the best way to change this from text to number?

Thanks a lot.

 

[AnotherHiggins]

Just to make sure, do you have an equal sign (=) before each formula?

Assuming you do....

This is a common problem with imported data. The cause is that the data is formatted as text. Even though you might have tried to change the formatting, even though you can check the formatting of the cells and Excel says they are formatted as number, they are really still formatted as text.

But the following steps will fix it for you:
[ul][li]In a cell to the right of all the imported data, type [blue]1[/blue][/li]
[ul][li](this should be right-aligned, indicating that the cell is formatted as a number)[/li][/ul]
[li]copy that cell[/li]
[li]select the cells that are giving you trouble^[red]*[/red][/li]
[li]Go to Edit > Paste Special > Multiply[/li]
[li]Now try applying a new format to those cells [/li][/ul]

^[red]*[/red] Note that any null cells that you select will be converted to zeros. For this reason I recommend that you don’t select an entire row/column.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[LNBruno]

Or you could use this formula in an adjoining column:

=(LEFT(A1,FIND("+",A1)-1))+(RIGHT(A1,FIND("+",A1)-1))

< M!ke >
Acupuncture Development: a jab well done.

 

[WinblowsME]

Click cell with the formula, hit F2 and hit F9.

 

[AnotherHiggins]

WinblowsME,

That works, but you'd have to do that to every single cell you want to convert. The 'times 1 Fix' described above can convert all of your cells at once.

One more thing to check if none of this has worked, CCNProjects: Press [Ctrl] + [`]. That is the keyboard shortcut for Tools > Options > View > Formulas.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.