# Microsoft Office Application Help - Excel Help forum > Excel General >  >  Find the last occurrence of a character in a string

## LAF

I would like to find the position of the last occurrence of a character in a string.  For example, I have a string with the following:  "c:\wwgpeTool\bids\Test File.GP$".  I would like to find out the position of the last '\' in the string because I want to pull the filename, 'Test File.GP$' into a variable.  I would like to avoid writing code to do this.  Are there some Excel functions I can use for this?

----------


## Paul

This formula pulls the filename from the string, it doesn't return the position of the last "\", though it can be done.



```
Please Login or Register  to view this content.
```

----------


## VBA Noob

Try

=RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1,"\","~",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

VBA Noob

----------


## LAF

Thank you!

----------


## dhwilcox35

It worked in Excel 2007 too.

----------


## Teethless mama

=trim(right(substitute(a1,"\",rept(" ",99)),99))

----------


## Marcol

To answer your question directly, this returns the position of the last occurrence of "\" in your string "c:\wwgpeTool\bids\Test File.GP$"


```
Please Login or Register  to view this content.
```


"|" (pipe) can be any character unlikely to be found in your string.

I would suggest avoiding "~" (tilde), it can in certain circumstances, cause Excel to crash.

----------


## LMS_Tech

> =trim(right(substitute(a1,"\",rept(" ",99)),99))



OMG, I just registered on this site to comment about this formula.  

This is just absolutely brilliant!  :Smilie:  Just brilliant.  :Cool: 

Dumb me, I would have gone blithely down the road illustrated by the following thinking.

These are for illustration purposes only, they are not formulas I've written nor tested for this project. Only to illustrate the "literal" way (compared to above) that I have always thought about writing excel formulas.





> =RIGHT(A1,LEN(A1)-SEARCH("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))







> =MID(MID(MID(SUBSTITUTE(A6," ","^",3),1,256),FIND("^",SUBSTITUTE(A6," ","^",3)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A6," ","^",3),1,256),FIND("^",SUBSTITUTE(A6," ","^",3)),256))-2)







> =SEARCH("^^",SUBSTITUTE(A1,"\","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))







> =LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\"),ROW(INDIRECT("1:"&LEN(A1))))

----------

