Value function (Excel 2000)

Topic

New Reply

I’ve inherited a spreadsheet which has all of the numbers somehow are being interpreted as text. I’ve tried formatting and I’ve tried the value function. I’ve also tried to use the trim function since there are leading spaces in the cells. Nothing seems to work. I’ve got 6 years of data. Too much to reenter. Any suggestions would be appreciated.

I’ve attached a snippet of the spreadsheet.

Reply | Quote

Replies

There seems to be a spurious “blank ” character embedded in your data. It is however not the standard blank (Chr(32)) but rather Chr(160). You can search and replace this character with “”. To enter the character in the Find what text box use Alt 0160 (hold down the Alt key and press 0160).

Or run the following code :

Sub CleanUp()
ActiveSheet.UsedRange.Replace _
What:=Chr(160), Replacement:=””
End Sub

Andrew C

Reply | Quote

That explains why trim and value didn’t work.

Can you explain how you diagnosed this so I could do it myself in the future?

Reply | Quote

I used the CODE function which returns the ASCII code of the first character in a string.

=CODE(C2) returned 160.

So did =CODE(RIGHT(C2,1)), indicatiing that the cell value had leading and trailing ‘spaces’

Andrew C

Reply | Quote