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?

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" wrote:

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?

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

"Tom Hutchins" wrote:

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" wrote:

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?

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

"STRAC" wrote:

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

"Tom Hutchins" wrote:

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" wrote:

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?

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.

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

On Wed, 1 Oct 2008 15:33:02 -0700, STRAC
wrote:

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

"Tom Hutchins" wrote:

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" wrote:

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?