Ms Excel: retrieve last numeric string after "\"char
Hello experts,
I have the following string C:\toto\titi\tt\3252.
I am looking for a formula in order to retrieve numeric value such as 3252. The complex thinks is that last numeric value doesn't always contains 4 number it can contains 5 or 6. The unique think which is always common is that the next left value of numeric string to retrieve is \ char.
Thank you very much for your help.
I have the following string C:\toto\titi\tt\3252.
I am looking for a formula in order to retrieve numeric value such as 3252. The complex thinks is that last numeric value doesn't always contains 4 number it can contains 5 or 6. The unique think which is always common is that the next left value of numeric string to retrieve is \ char.
Thank you very much for your help.
I don't want any credit but Roy probably meant this.
=RIGHT(A1,LEN(A1)-FIND("\",A1,1))
=RIGHT(A1,LEN(A1)-FIND("\",A1,1))
Just trying to change it but you beat me Martin, I also meant "assuming" ( clumsy fingers!!!)
ASKER
Tested but it doesn't work
Example:
Instead of having:
555685
Example:
Instead of having:
555685
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Martin. I tested and it works.
Thank you very much for your help.
Thank you very much for your help.
You’re welcome and I’m glad I was able to help.
If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.
Marty - Microsoft MVP 2009 to 2017
Experts Exchange Most Valuable Expert (MVE) 2015, 2017
Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.
Marty - Microsoft MVP 2009 to 2017
Experts Exchange Most Valuable Expert (MVE) 2015, 2017
Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
To explain the functions that Martin used:
The SUBSTITUTE function will look for specified characters and replace them with another specified character(s).
LEN function returns the number of characters in the string
REPT function repeats a character the specified number of times.
RIGHT function returns specified number of characters from the right hand end of the string.
TRIM function removes leading or trailing spaces and multiple central spaces get converted to a single space.
In this instance all of the occurrences of \ are being replaced with a \ and repeated spaces, the space being repeated as many times as the ORIGINAL length of the string.
The RIGHT function is then looking at the right hand end of the amended string and returning the length of the original string; this will be the last characters and however many spaces it needs to be equivalent to the original length. The TRIM function then removes the surplus spaces.
The SUBSTITUTE function will look for specified characters and replace them with another specified character(s).
LEN function returns the number of characters in the string
REPT function repeats a character the specified number of times.
RIGHT function returns specified number of characters from the right hand end of the string.
TRIM function removes leading or trailing spaces and multiple central spaces get converted to a single space.
In this instance all of the occurrences of \ are being replaced with a \ and repeated spaces, the space being repeated as many times as the ORIGINAL length of the string.
The RIGHT function is then looking at the right hand end of the amended string and returning the length of the original string; this will be the last characters and however many spaces it needs to be equivalent to the original length. The TRIM function then removes the surplus spaces.
=RIGHT(A1,LEN(A1)-FIND("/"