Number Format Issue

[mrmmickle1]

I have a column that has text numbers in it and I am having trouble converting the text to numbers.

I have tried:

1.Number Format, General Format

2. This macro:

[COLOR=#0000ff]Sub[/COLOR] FormatTexttoNumbers()
   [COLOR=#0000ff] Dim[/COLOR] Col [COLOR=#0000ff]As [/COLOR]Range
[COLOR=#0000ff]        On Error Resume Next[/COLOR]
          [COLOR=#0000ff]  Set[/COLOR] Col = Application.InputBox("Please select a column...", Type:=8)
          [COLOR=#0000ff]  Set[/COLOR] Col = Columns(Col.Cells(1).Column)
        Col.NumberFormat = "0.00"
        Col.Value = Col.Value
[COLOR=#0000ff]  On Error GoTo 0[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

3. =NUMBERVALUE(A1)

4. Copy and Paste As Values

5. They test FALSE for =ISNUMBER()

They look like this:

Excel 2012
	C	D
2	100.1	9992
3	1011.4	988
4	10.2	98348
5	1028.7	972
6	103.1	9696
7	10.7	93276
8	1074.5	930
9	1079.6	926
10	1094.0	914
11	111.9	8933
12	1130.0	884
13	113.8	8789
14	116.7	8568
15	1171.4	853
Sheet1

does anyone know why this would be occurring or how to fix my issue? Any help would be appreciated.

 

[sheetspread]

Did you try highlighting the cells and running selection.value = selection.value in the immediate window?


If that doesn't work change the format to number and run it again

 

[henrik2h]

Try copy a zero and pastespecial add values over the range

 

[mrmmickle1]

sheetspread & henrik2h,

Thank you for the assistance. I found that the issue was this:

Column C & D have two trailing spaces for every value meaning that a cell value that appears to be 100.1 is actually “100.1 “ . These two trailing spaces prevented me from simply changing the cell format to “Number Format”. Instead I had do this:

1. Use these two formulas to get rid of trailing spaces=LEFT(C2,LEN(C2)-2) & =LEFT(D2,LEN(D2)-2) (Copy Down)
2. Use Selection.Value = Selection.Value

So when I tried to use Selection.Value = Selection.Value initially it would not work because these values were not recognized as numbers.

Note: I tried using the Trim() Function first and it failed to correct the issue...

 

[sheetspread]

Glad you got it working, but when I try:

Excel 2010
	A
1	3
2	44
3	22
4	100.1
5	220.33
6	234.56
7	=SUM(A1:A6)
Sheet1

(first formatting the A column cells as text, you can see the sigma button and range returns the formula)

then I format the column as number:

Excel 2010
	A
1	3
2	44
3	22
4	100.1
5	220.33
6	234.56
7	=SUM(A1:A6)
Sheet1

still no change, but if I then run the selection.value = selection.value after highlighting the A column numbers:

Excel 2010
	A
1	3.00
2	44.00
3	22.00
4	100.10
5	220.33
6	234.56
7	623.99
Sheet1

Cell Formulas
Range	Formula
A7	=SUM(A1:A6)

No trim needed (some of the numbers had preceding or ending extra spaces)

Also, did ctrl-h find space, replace with nothing work?