cansevin
asked on
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
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
Format the col as currency ?
Copy the col out to another sheet first.
Copy the col out to another sheet first.
ASKER
Yes. See attached. Thanks for the help!
ASKER
Here it is
Bad-Numbers.xlsx
Bad-Numbers.xlsx
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.
Public Sub loopy()
Dim i As Long
Dim wS As Worksheet
Set wSheet = ActiveWorkbook.Worksheets(
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
Dim i As Long
Dim wS As Worksheet
Set wSheet = ActiveWorkbook.Worksheets(
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
Please try this...
Sub FixAllNumbers()
ActiveSheet.UsedRange.Replace Chr(160), ""
End SubASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Public Sub loopy()
Dim i As Long
Dim wS As Worksheet
Set wSheet = ActiveWorkbook.Worksheets(
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
Dim i As Long
Dim wS As Worksheet
Set wSheet = ActiveWorkbook.Worksheets(
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
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(SUB
Thanks
Rob H
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(SUB
Thanks
Rob H
Could you send a dummy example?
Regards