Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting spaces
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting spaces
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting spaces
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting spaces
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 "dstiefe" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting spaces
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting spaces
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 , dstiefe wrote: my "trim" function is not working.. any thoughts on that? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Inbetween Spaces | Excel Discussion (Misc queries) | |||
how do I remove leading spaces and leave the remianing spaces w | Excel Worksheet Functions | |||
Counting the length of a string and adding spaces... | Excel Programming | |||
counting spaces in a string | Excel Programming | |||
counting spaces in a string | Excel Programming |