Excel - Text to Number

[fishax]

Hello,
I have an Excel Sheet with what appears to be Numbers, but are actually Text. I need a way to convert these to Numbers using an automated formula or Macro. Can anyone point me in the right part what Excel has to be able to convert Text to Numbers?

 

[SkipVought]

Select the column.

Data > text to columns

Fixed Width

Do not insert ANY delimter lines

Select General

Finish

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[AnotherHiggins]

If you want to do it via code:

Range("A:A").value = Range("A:A").value

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

Help us help you. Please read FAQ 181-2886 before posting.

 

[fishax]

Unfortunately it did not work (I did not try it via code, but I believe this would fail as well).

It may be because of spaces in the values entered. Values are in the format as follows:

{space}{space}35.34{space} or
{space}{space}{space}2.84{space} or
{space}345.67{space}

If I re-enter the values, it is fine (they convert to numbers), but there are just too many to start entering manually.

 

[AnotherHiggins]

Have a look at the TRIM function.

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

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

=VALUE(TRIM(Cell_Ref))

or

Range("A:A").value = val(trim(Range("A:A").value))


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[combo]

For quick conversion you can run this procedure for selected range:

Sub Str2Val()
Dim c As Range
On Error Resume Next
For Each c In Selection.Cells
    c.Value = Trim(c.Value) + 0
'    c.Value = Replace(c.Value, " ", "") + 0 ' in case of internal spaces
Next c
End Sub

combo

 

[SkipVought]

[tt]
{space}{space}35.34{space} or
{space}{space}{space}2.84{space} or
{space}345.67{space}
[/tt]

This is why a good post ought to include ALL the relevant information. Otherwise, you will recieve suggestions that do not apply.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]