I have a column full of names...the problem is there is random spacing before
the name...for example: " name" and in others it may be " name" while
others are " name"

how do I first count the number of spaces? then delete them so the name is
at the far left?

thank you

Hi,

to remove unwanted space in cell A1, for example

=TRIM(A1)

Copy down as necessary. Then copy all the formulas, and choose Edit, Paste
Special, Values. You can then remove the originals and keep the clean
version.


--
Thanks,
Shane Devenshire


"dstiefe" wrote:

> I have a column full of names...the problem is there is random spacing before
> the name...for example: " name" and in others it may be " name" while
> others are " name"
>
> how do I first count the number of spaces? then delete them so the name is
> at the far left?
>
> thank you

Oops,

I see you wanted to count the spaces. Do you want to count the number for
each cell or for all the cells?

This will count all the space in your text
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
assuming this are regular spacebar spaces. It will count the single space
between Happy Birthday.

This will count all the spaces that would be removed by TRIM
=LEN(A1)-LEN(TRIM(A1))
TRIM removes all leading and trailing spaces and all but one of the spaces
between word. It only works for spacebar spaces.

If you want to do the above for all the cells in column A but get one answer
in a single cell then

=SUMPRODUCT(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100," ","")))
or
=SUMPRODUCT(LEN(A1:A100)-LEN(TRIM(A1:A100)))

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"dstiefe" wrote:

> I have a column full of names...the problem is there is random spacing before
> the name...for example: " name" and in others it may be " name" while
> others are " name"
>
> how do I first count the number of spaces? then delete them so the name is
> at the far left?
>
> thank you

One way to fix it and count the names at the same time
Sub trimitupandcount()
mc = 0
For Each c In Range("a2:a22")
c.Value = LTrim(c)
If LCase(Left(c, 4)) = "name" Then mc = mc + 1
Next c
MsgBox mc
End Sub

If you DONT want to count the names,comment out the IF line


Sub trimitup()
For Each c In Range("a2:a22")
c.Value = LTrim(c)
Next c
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"dstiefe" <(E-Mail Removed)> wrote in message
news:FD3818E5-E7EF-400F-9F4E-(E-Mail Removed)...
>I have a column full of names...the problem is there is random spacing
>before
> the name...for example: " name" and in others it may be " name" while
> others are " name"
>
> how do I first count the number of spaces? then delete them so the name is
> at the far left?
>
> thank you

my "trim" function is not working.. any thoughts on that?

"ShaneDevenshire" wrote:

> Hi,
>
> to remove unwanted space in cell A1, for example
>
> =TRIM(A1)
>
> Copy down as necessary. Then copy all the formulas, and choose Edit, Paste
> Special, Values. You can then remove the originals and keep the clean
> version.
>
>
> --
> Thanks,
> Shane Devenshire
>
>
> "dstiefe" wrote:
>
> > I have a column full of names...the problem is there is random spacing before
> > the name...for example: " name" and in others it may be " name" while
> > others are " name"
> >
> > how do I first count the number of spaces? then delete them so the name is
> > at the far left?
> >
> > thank you

Perhaps the source of your data included non-breaking spaces (e.g., in a
browser):

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

In article <9D1551B9-44B0-4E7B-9930-(E-Mail Removed)>,
dstiefe <(E-Mail Removed)> wrote:

> my "trim" function is not working.. any thoughts on that?