Removing a leading space in a numbered currency cell

[STRAC]

I have a spreadsheet which was imported from another program. I want to
total up the currency columns but find that each cell contains a leading
space which prevents Excel from doing an auto sum on the column. How do I
delete the leading space without having to go into each individual cell (2000
plus) and delte the space?

 

[Tom Hutchins]

Select all the cells with the leading space. Select Edit >> Replace. In the
Find What box, enter a single space (press the spacebar once). Leave the
Replace With box empty. Click the Replace All button, then Close. You may
then also need to reformat the cells. With the cells still selected, select
Format >> Cells. On the Number tab, select General, then click OK.

Hope this helps,

Hutch

 

[STRAC]

Excel returns an error message reading "Excel cannot find any data to
replace"...

 

[JLGWhiz]

Try selecting the range with the leading space then run this short sub from
the standard code modue 1.

Sub fixSpaces()
Dim c As Range
For Each c In Selection
c = c.Value
Next
End Sub

 

[Dave]

Hi, Try this:
Select all data cells and change format to General.
Put a zero into a cell, select it, then Copy.
Select all data cells.
Paste special, Add
OK

Regards - Dave.

 

[Gord Dibben]

Your space may be a non-breaking space and Excel won't see it a regular
space.

Try Alt + 0160(on the numpad) in the Find What:

Or run this macro.

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace what:=Chr(160), replacement:=Chr(32), _
lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each Cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
Cell.Value = Application.Trim(Cell.Value)
Next Cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP