Delete Variable spaces at beginning of a range
 

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

Delete Variable spaces at beginning of a range
 

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

Delete Variable spaces at beginning of a range
 

On Aug 21, 9:54 am, JW wrote:
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 wrote:
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- Hide quoted text -

- 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?

Delete Variable spaces at beginning of a range
 

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

gmunro wrote:
On Aug 21, 9:54 am, JW wrote:
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 wrote:
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- Hide quoted text -

- 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?

Delete Variable spaces at beginning of a range
 

On Tue, 21 Aug 2007 05:35:11 -0700, gmunro wrote:

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