I can’t even remember why now but fairly recently I have needed to take a string/text in excel and find the last occurence of a particular character so that I could manipulate the data. Despite searching the internet all I could find was complex functions that didn’t quite do the job and unforutnately Excel doesn’t come with any inbuilt functions like for example ’strrchr’ in PHP.
Surely you would think this was easy to do and documented somewhere but I couldn’t find anything so set out to use some simple inbuilt Excel functions without writing Excel modules. The following is a working example and some theoritical code.
Say for example I have the following in cell A1 - c:\directory\temp1\temp2\temp3 and want to be able to remove the last directory. To be able to do this I first need to find the last backslash \ and then remove the text from there. Sounds easy but the is no function for finding the last character, if there was only one backslash then no problem but there are multiple.
Step 1 (data in cell A1 forumla in B1)
First you need to be able to count the the number of occurences of the character, the following forumla will do just that :
which hopeful returns 4
Step 2 (data in cell B1 forumla in C1)
Thankfully Excel does come with an inbuilt substitution function which will allow you to change a character for a certain occurance. So next you need to replace the final backslash with something unique which in this case is the asterix.
this should then return c:\directory\temp1\temp2*temp3
Step3 (data in cell C1 forumla in D1)
Now you have a unqiue charater you should be easily able to find the postition of this within the string using :
which should return 25
Step 4 (data in cell E1 forumla in D1)
You can then easily display all the text to the left of the last backslash or to the right of the backslash using the following :
=MID(A1,1,D1) returns c:\directory\temp1\temp2\
=RIGHT(A1,LEN(A1)-D1) returns temp3
Step 5 (data in cell A1 forumla in B1)
The complete forumla in cell B1 for both scenarios would be :
Obviously if you are changing multiple fields and the strings all have the same number of characters you are trying to match ie 4 backslashes then in theory you do not need to do step 1 because you should already no the number of occurences is 4. The above exmample is for scenarios where you may not always have the same formatted data.
If there are easier ways then let me know