Counting a Character

[TCF]

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

 

[Jarek Kujawa]

use this UDF

insert "." as char argument

Function leftchr(char As String, rng As range) As Integer
Dim i As Integer
Dim counter As Integer

For i = 1 To Len(rng.Text)
If Mid(rng.Text, i, 1) = char Then
counter = counter + 1
Else
Exit For
End If
Next i

leftchr = counter

End Function

 

[Dave Peterson]

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

 

[Ron Rosenfeld]

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

When you write "left-most" characters, do you mean to start at the first
character? Or at the first left-most instance of the search string?

In other words, what would you want to return, searching for ".", if the string
were:

ABC...PLATE - WELD, TAPPED, .312 - 18

0 or 3?

--ron

 

[JLatham]

Well, if "left most" means all but the last one, then just modify the formula
to
=LEN(A1)-LEN(SUBSTITUTE(A1,".","") -1

but if there's some other definition of "left most", then to come up with a
solution we need a clearer definition.

 

[Teethless mama]

=MIN(IF(ISERROR(FIND(CHAR(ROW(INDIRECT("65:90"))),A1)),"",FIND(CHAR(ROW(INDIRECT("65:90"))),A1)))-1

ctrl+shift+enter, not just enter

 

[TCF]

I only want to count the "." on the left side of the string until i hit
another character other than a "."