Excel – Find the last word

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.

Word Count

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.

Last word 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.

=MID(A1,FIND("$",D1)+1,LEN(A1)-FIND("$",D1))

Last word of string Excel

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.

=LEFT(A1,FIND("$",D1))

Finally we have segregated the given text into its expense heads and expense values.

You can download the excel workbook here Last Word.

Advertisements

About Paripurnachari

Finance Professional, Coder, Engineer
This entry was posted in Technology and tagged , , . Bookmark the permalink.

2 Responses to Excel – Find the last word

  1. Pingback: NSE Live Stock (Share Price) Data into Excel | Thinking Through Excel A1

  2. Pingback: BSE Live Stock (Share Price) Data into Excel | Thinking Through Excel A1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s