Converting text values to number values in excel

[Hyperlite]

I am working with an excerpt from a database. i don't have the db file, just the data dropped into excel. the xls came with a disclaimer stating some of the data moved from the db file may become a text value in the cell, IE, not usable in a numerical formula. my question is, how can i convert these text values to numerical values? i have gone through excel's guides with no avail. anyone?

 

[Hadsus]

Try adding zero to all affected cells. Then copy 'em back to their original location by using copy, paste special, values (to avoid circular reference). If you have alot of data, it could be a PITA.

 

[Aikouka]

Can't you just highlight the cells you want to change, right click, go to "Format Cells...", and in the initial pane (Number tab), change the Category from "Text" to "Number"?

 

[Hyperlite]

Originally posted by: Aikouka
Can't you just highlight the cells you want to change, right click, go to "Format Cells...", and in the initial pane (Number tab), change the Category from "Text" to "Number"?

well that would be the obvious solution, but i have tried every possible method i have found online. the data in the cells is not left aligned, so i am beinging to think its not even text and something is just wrong. i am going to go back to the source and get a new copy of the data.

thanks for the suggestions :thumbsup:

 

[iDazzler]

Hi Hyperlite,

I had the same issue a while back and it has to do with an space ( ASCII Char(160) ) being passed into the cell. What you have to do is format that area to strip out the extra space and then do you manipulation.

Here's the function I had recorded then manipluted but anything similar will work:

Sub SelectCurrentRegionAndFormat()

Dim Col As Range

ActiveCell.CurrentRegion.Select
ActiveCell.CurrentRegion.Name = "LPDATA"

ActiveCell.CurrentRegion.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

For Each Col In ActiveCell.CurrentRegion.Columns
If Application.CountA(Col) > 0 Then
Col.TextToColumns Destination:=Col(1), DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
End If
Next Col


End Sub


Bottom line write function to strip out extra ascii character 160 and you're good to go...

Dan

 

[Hyperlite]

Originally posted by: iDazzler
Hi Hyperlite,

I had the same issue a while back and it has to do with an space ( ASCII Char(160) ) being passed into the cell. What you have to do is format that area to strip out the extra space and then do you manipulation.

Here's the function I had recorded then manipluted but anything similar will work:

Sub SelectCurrentRegionAndFormat()

Dim Col As Range

ActiveCell.CurrentRegion.Select
ActiveCell.CurrentRegion.Name = "LPDATA"

ActiveCell.CurrentRegion.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

For Each Col In ActiveCell.CurrentRegion.Columns
If Application.CountA(Col) > 0 Then
Col.TextToColumns Destination:=Col(1), DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
End If
Next Col


End Sub


Bottom line write function to strip out extra ascii character 160 and you're good to go...

Dan

you are my hero. that worked like a charm. thanks so much!!

 

[iDazzler]

You're welcome.