Character count

[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 "\"

 

[PCLIVE]

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

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Rick Rothstein \(MVP - VB\)]

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 "\"

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

 

[Guest]

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,

 

[dan]

Hi Rick,
Your formula is very useful to me. Thank you very much.

 

[dan]

Thank you, Farhad,
I like its thoroughness

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,

 

[dan]

Very nice, Thank you

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

 

[dan]

Thank you

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