How often have you wanted to badly extract text from the right of a text, just the way the LEFT function works from the left. I’ve just craved for a function like
and it picks the characters from the right counting till it finds the ‘,’!
For instance, “Columbus, OH” is the text in a cell and you need to extract “OH” from the right after the ‘,’ – one way is to find where the comma exists and then find the number of characters from the right by subtracting the total length of string from it… too tedious! But that works…!
Here’s how it looks
= RIGHT(“Columbus, OH”, LEN(“Columbus, OH”) – FIND(“,”,”Columbus, OH”,1))
Now is that all? Have we a universal solution at hand? Or should I say, have I no more problems at hand? Well, as usual, the answer is ‘not really’! What if you had multiple commas in a text string – like “Columbus, OH, United States, 40023″ and you wanted the last part after the ‘,’ i.e. 400023. If we went about working the solution like above, we’ll have Three FINDs to find the location of the last ‘,’!
Worse still?? What if you didn’t know how many commas’s existed in each cell, i.e. if the number of commas in every cell is not fixed! Now that’s a problem and we sure would love to have a function like FINDRIGHT I mentioned above!
What’s interesting is, there indeed is a nice unique solution to this problem, quite innovative I’d say.. great logic!
Here’s the idea – how about if we counted the number of occurrences of the delimiter per cell, and then, temporarily change that delimiter to another delimiter and use that in the FIND function I’ve mentioned above. Voila!!
Find number of occurrences (this is the most important step in all this)
= LEN(A1)-LEN(SUBSTITUTE(A1,”,”,”")) (What a great way to get the number of occurrences – very thoughtful!)
Substitute the last occurrence only!
= SUBSTITUTE(A1,”,”,”|”, … ) (… follows from the function above which gives the instance to be substituted)
Extract using the first formula above
RIGHT(A1, LEN(A1) – FIND(“|”,…,1))
Putting them all together -
And there we have it…
Now this is also not without shortcomings, but we’ll take that up in another post. For now, this is a great tool to use, and just copy that last function and put it in the cell next to where you want to extract!
One great link that helped me with this is given below for reference -