Delete Variable spaces at beginning of a range

[gmunro]

Hi,

I pull a report from a source I don't control.
The report has a list starting in cell B27 and is of variable length.

The problem I have is the programmers who I cannot influence, change
the number of spaces at the beginning of the list, and I use this
report for a vlookup, so exact match is required

They went from 15 spaces before teh data to none to 14.

How can I count the number of spaces and replace with "" in a specific
range?

Any help is appreciated

Glen

 

[JW]

Any other spaces in the cell? If not, you can use a substitute
formula to replace all empty spaces with nothing.
=SUBSTITUTE(C4," ","")
To get rid of all of the spaces except those separating words, use a
trim formula
=TRIM(C4)

HTH
-Jeff-

 

[gmunro]

Any other spaces in the cell? If not, you can use a substitute
formula to replace all empty spaces with nothing.
=SUBSTITUTE(C4," ","")
To get rid of all of the spaces except those separating words, use a
trim formula
=TRIM(C4)

HTH
-Jeff-










- Show quoted text -

Can you trim a selected range? or do you need to use the trim function
in a separate column and then copy/paste your data?

 

[JW]

The Trim formula would be in a separate cell. You could use VBA to
loop through the range and Trim the cells though. Something like:
sub untested()
Dim cl as Range, clRange as Range
Set clRange=Range("A2:A50")
For Each cl in clRange
cl.Value=Trim(cl.Value)
Next cl
Set clRange=Nothing
End Sub

 

[Ron Rosenfeld]

Hi,

I pull a report from a source I don't control.
The report has a list starting in cell B27 and is of variable length.

The problem I have is the programmers who I cannot influence, change
the number of spaces at the beginning of the list, and I use this
report for a vlookup, so exact match is required

They went from 15 spaces before teh data to none to 14.

How can I count the number of spaces and replace with "" in a specific
range?

Any help is appreciated

Glen

In Excel you can use the TRIM function to remove both leading and trailing
spaces as well as extra (more than one sequential) spaces within the text.

In VBA you can use the LTrim function to remove only leading spaces.


--ron