Trim cells in excel

I have a column of cells that has in Column F. They look like a dollar value ie: $4,124.15 but they are not acting like one. They were copied and pasted into the sheet from the internet. I believe every one of them has two spaces after the value (obviously invisible).

What is the easiest way to get them to act like numbers? I believe the two spaces after it is what is making it act weird. I tried Trim(F1) and it doesn't help. Any other options?

Thanks!

Chris
cansevinAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
HI,

to change the values in number to be formatted afterwards then try
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,CHAR(160),""),",",""),"$","")

Open in new window

Regards
Bad-NumbersV1.xlsx
0
 
Rgonzo1971Commented:
Hi,

Could you send a dummy example?

Regards
0
 
Jonathan KellyCommented:
Format the col as currency ?

Copy the col out to another sheet first.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
cansevinAuthor Commented:
Yes. See attached. Thanks for the help!
0
 
cansevinAuthor Commented:
Here it is
Bad-Numbers.xlsx
0
 
tomfarrarCommented:
Are the numbers showing up on the left side of the cell?  Probably considered text.  The data may need to be trimmed (get rid of excess spaces) using the =trim() function, then converted to a number using (I think) the =value() function.  An example would be helpful as suggested earlier.
0
 
Jonathan KellyCommented:
Public Sub loopy()
Dim i As Long
Dim wS As Worksheet

Set wSheet = ActiveWorkbook.Worksheets("sheet1")


For i = 1 To Rows.Count
    If InStr(1, wSheet.Cells(i, 3).Value, Chr(32)) Then
        wSheet.Cells(i, 3).Value = Replace(wSheet.Cells(i, 3).Value, Chr(32), "")
    End If
Next i
End Sub
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please try this...

Sub FixAllNumbers()
ActiveSheet.UsedRange.Replace Chr(160), ""
End Sub

Open in new window

0
 
Jonathan KellyCommented:
Public Sub loopy()
Dim i As Long
Dim wS As Worksheet

Set wSheet = ActiveWorkbook.Worksheets("sheet1")


For i = 1 To Rows.Count
    If InStr(1, wSheet.Cells(i, 3).Value, Chr(160)) Then
        wSheet.Cells(i, 3).Value = Replace(wSheet.Cells(i, 3).Value, Chr(160), "")
        Debug.Print wSheet.Cells(i, 3).Value
    End If
Next i
End Sub
0
 
Rob HensonFinance AnalystCommented:
The accepted solution still leaves the values as text. If using directly in an onward calculation that won't matter.

When re-calculating a reference to a single cell Excel will re-evaluate the contents of the cell and will recognise it as a number and deal with it.

However, summing a range of cells that look like numbers but are still text won't work.  

To amend the accepted formula just add *1 on the end ie:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,CHAR(160),""),",",""),"$","")*1

Thanks
Rob H
0
All Courses

From novice to tech pro — start learning today.