Number stored as text

[caritx]

I have a column that has both numbers stored as text and text. I need the numbers to be stored as number.

I have tried to (a1*1) but then on the text I get #value. Trim didn't help either. This is really frustrating because my vlookup won't accept number stored as text.

Any help you can offer I really appreciate it. I tried searching the board first before asking a question that was easy to find.

 

[VoG]

Try selecting them, Data > Text to Columns and click the Finish button.

 

[doofusboy]

=if(isnumber(a1),value(a1),a1)

 

[caritx]

Try selecting them, Data > Text to Columns and click the Finish button.

That worked. Thanks I knew it had to be something super easy. Thanks again!

 

[caritx]

Try selecting them, Data > Text to Columns and click the Finish button.

I was trying to get my macro recorder to do the same steps and it failed. See code:

Selection.TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

 

[VoG]

Try

With Selection
    .Value = .Value
End With

 

[caritx]

Thanks!