Count specific character in text string

[Alex Burman]

I know this can be done, but I've forgotten. What is the formula to count
the number of, for example, the letter "d" in a text string in a cell?

Also, I have imported a list of path and file names into Excel. Is there a
formula/function that will allow me to display the file name and remove the
path. e.g.

c:\temp\asg\123\text file.doc to text file.doc

I would like it to be a cell formula rather than using "text to columns".

Many thanks, Alex

 

[Dave Hawley]

Hi Alex

To count characters use

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

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[JE McGimpsey]

one way:

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

 

[Dave Hawley]

Sorry, forgot your second question. I have a Custom Function here that
makes this easy

http://www.ozgrid.com/VBA/GetExcelFileNameFromPath.htm

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[JE McGimpsey]

One way:

=MID(A1, FIND("^", SUBSTITUTE(A1, "\", "^", LEN(A1) -
LEN(SUBSTITUTE(A1, "\", "")))) + 1, 255)

 

[Alex Burman]

Perfect, Thanks v much to you both.

One way:

=MID(A1, FIND("^", SUBSTITUTE(A1, "\", "^", LEN(A1) -
LEN(SUBSTITUTE(A1, "\", "")))) + 1, 255)

columns".