Character count

  • Thread starter Thread starter dan
  • Start date Start date
D

dan

Is there a formula that returns a count of a specified character?
For example:
Cell(A1) = I:\1stFolder\2ndFolder\3thFolder\4thFolder\5thFolder\abc.xls
Answer would be 6 for "\"

And also a formula that returns a folder name between 2 consecutive "\"
 
To get a count of the number of times "\" appears:
=LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))

Which folder name do you want? There are 5 folders.

Regards,
Paul
 
say a1 has the full text and a2 has the character to be counted:

=LEN(A1)-LEN(SUBSTITUTE(A1,A2,""))

For the data between \
use Text to Columns
 
Try Data>Text To Columns for the second part.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Is there a formula that returns a count of a specified character?
For example:
Cell(A1) = I:\1stFolder\2ndFolder\3thFolder\4thFolder\5thFolder\abc.xls
Answer would be 6 for "\"

And also a formula that returns a folder name between 2 consecutive "\"
Click to expand...

For the second question, if you really need a formula (that is, if Bob's
TextToColumn suggestion isn't usable for some reason), then give this a
try...

=IF(AND(B1>0,B1<LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))),MID(LEFT(A1,FIND("*",SUBSTITUTE(A1&"\","\","*",B1+1))-1),1+FIND("*",SUBSTITUTE(A1,"\","*",B1)),260),"")

It assumes the folder number you are looking for (1 for the the first
folder, 2 for the second one, etc.) is in B1. The formula returns the empty
string if zero, a negative value or a value that would take you up to (or
beyond) the file name itself.

Rick
 
Hi,

If you are looking to count repetitions of a word (more than one character)
for example the word "Folder" in your text string then you should change the
Gary's formula to:

=(LEN(A1)-LEN(SUBSTITUTE(A1,A2,"")))/LEN(A2)

Thanks,
 
Thank you, Farhad,
I like its thoroughness

Farhad said:
Hi,

If you are looking to count repetitions of a word (more than one
character)
for example the word "Folder" in your text string then you should change
the
Gary's formula to:

=(LEN(A1)-LEN(SUBSTITUTE(A1,A2,"")))/LEN(A2)

Thanks,
Click to expand...
 
Very nice, Thank you

PCLIVE said:
To get a count of the number of times "\" appears:
=LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))

Which folder name do you want? There are 5 folders.

Regards,
Paul
Click to expand...
 
Thank you

Gary''s Student said:
say a1 has the full text and a2 has the character to be counted:

=LEN(A1)-LEN(SUBSTITUTE(A1,A2,""))

For the data between \
use Text to Columns
Click to expand...
 
Back
Top