Is there a formula that returns a count of a specified character?
For example:
Cell(A1) = I:\1stFolder\2ndFolder\3thFolder\4thFolder\5thFold er\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




--

"dan" wrote in message
news:97QKi.4076$WT2.3028@trndny05...
Is there a formula that returns a count of a specified character?
For example:
Cell(A1) = I:\1stFolder\2ndFolder\3thFolder\4thFolder\5thFold er\abc.xls
Answer would be 6 for "\"

And also a formula that returns a folder name between 2 consecutive "\"

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
--
Gary''s Student - gsnu200747


"dan" wrote:

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

And also a formula that returns a folder name between 2 consecutive "\"

Try DataText To Columns for the second part.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"dan" wrote in message
news:97QKi.4076$WT2.3028@trndny05...
Is there a formula that returns a count of a specified character?
For example:
Cell(A1) = I:\1stFolder\2ndFolder\3thFolder\4thFolder\5thFold er\abc.xls
Answer would be 6 for "\"

And also a formula that returns a folder name between 2 consecutive "\"

Is there a formula that returns a count of a specified character?
For example:
Cell(A1) = I:\1stFolder\2ndFolder\3thFolder\4thFolder\5thFold er\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(B10,B1<LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))),MID(LEFT(A1,FIND("*",S UBSTITUTE(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,
--
Farhad Hodjat


"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200747


"dan" wrote:

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

And also a formula that returns a folder name between 2 consecutive "\"

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

"Rick Rothstein (MVP - VB)" wrote in
message ...
Is there a formula that returns a count of a specified character?
For example:
Cell(A1) = I:\1stFolder\2ndFolder\3thFolder\4thFolder\5thFold er\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(B10,B1<LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))),MID(LEFT(A1,FIND("*",S UBSTITUTE(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

Thank you, Farhad,
I like its thoroughness

"Farhad" wrote in message
...
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,
--
Farhad Hodjat


"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200747


"dan" wrote:

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

And also a formula that returns a folder name between 2 consecutive "\"

Very nice, Thank you

"PCLIVE" wrote in message
...
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" wrote in message
news:97QKi.4076$WT2.3028@trndny05...
Is there a formula that returns a count of a specified character?
For example:
Cell(A1) = I:\1stFolder\2ndFolder\3thFolder\4thFolder\5thFold er\abc.xls
Answer would be 6 for "\"

And also a formula that returns a folder name between 2 consecutive "\"

Thank you

"Gary''s Student" wrote in message
...
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
--
Gary''s Student - gsnu200747


"dan" wrote:

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

And also a formula that returns a folder name between 2 consecutive "\"