On one of these days, I have received a following text from one of my friend who scribbled his various expenses in notepad and asked me help me tabulating his expenses.
Rent 4,000 Tuition Fees 8,000 Canteen Food Expenses 2,000 Road trip to Munich 1,100 News Media Yearly Subscription 500 Birthday Party Expenses 600
To tabulate the data, I copy pasted the data into my Excel workbook. Now I need to segregate the given data into expense head and expense value. The only pattern I could find is the expense value is the last word in the texts given.
My initial thought is to run the text to columns wizard and get the expenses easily. But the expense heads are of different word lengths resulting the expenses in different columns. Though there are other ways to solve this problem (Array functions, REPT function to make some pattern, Replace with escape sequence …etc.), I thought there should be a easy way out to get last word of the expense.
After recollecting all the text functions in Excel, I thought simple “Right” function will solve the problem. But the expense values are of different digits which is constraining the “Right” function to give the desired result.
The only pattern I could find is the characters after the last space is my expense values. Now how do I locate the last space and how do I find how many spaces are there in each expense.
To find how many words / spaces in the string, replace all the spaces in text and difference between new string and initial string shall give the number of words / spaces. Here we are using Substitute function to substitute all the spaces in the string.
To find the last word / space, we will use the Substitute function again but just to substitute the last space by inputting the number of spaces as instance number. We can use any delimiter to find its location as the substitute character. Here I am using $ character as a delimiter.
Once we have the delimiter set at just before the last word, the desired task of getting the last word is achieved. Now we just need to find the delimiter and get the text from there to till last characters. We use Find, Len, Mid Excel functions to perform this task. Find function is used to find the delimiter character, Mid function is used to get the text from start of delimiter to till the last character of the string. Difference of location of delimiter character and length of string will the remaining characters in the string i.e. number of digits in the expense.
Now we have the last word of each expense, which is our expense. Expense heads can be obtained by taking the left portion of delimiter location.
Finally we have segregated the given text into its expense heads and expense values.
You can download the excel workbook here Last Word.