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

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

On Aug 21, 9:54 am, JW <JWRIGH...@triad.rr.com> 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?

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 <JWRIGH...@triad.rr.com> 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?

On Tue, 21 Aug 2007 05:35:11 -0700, gmunro <(E-Mail Removed)> 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