Re: Counting a Character
- From: Dave Peterson <petersod@xxxxxxxxxxxxxxxx>
- Date: Tue, 03 Nov 2009 07:42:57 -0600
One way...
=SEARCH(LEFT(TRIM(SUBSTITUTE(A1,"."," ")),1),A1)-1
=substitute() replaces all the dots with spaces.
Then =trim() removes the leading spaces (and trailing and changes multiple
consecutive spaces to a single space.
Then the =left() takes the first character of that trimmed string and looks for
its position in the original string.
And then the formula subtracts 1.
If you could have something like:
.......
(no additional characters), you can add a dummy character to the string and do
the same thing:
=SEARCH(LEFT(TRIM(SUBSTITUTE(A1&"x","."," ")),1),A1&"x")-1
TCF wrote:
I have seen how to count the number of occurances of a particular character,
but is there a way to limit the count to just the left most characters? here
is my example
...PLATE - WELD, TAPPED, .312 - 18
i want to count only the left most "." in this case, it would be 3. If I
use the
=LEN(A1)-LEN(SUBSTITUTE(A1,".","") i get 4 because of the . in the .312.
any help would be appreciated.
thanks
--
Dave Peterson
.
- References:
- Counting a Character
- From: TCF
- Counting a Character
- Prev by Date: RE: How to take text from other cells to create a reference to a name
- Next by Date: Median formula
- Previous by thread: Re: Counting a Character
- Next by thread: Re: Counting a Character
- Index(es):
Relevant Pages
|
