Working with some log files based on a Flat file, we use Excel to get the data in a structured way to be able to use against other datasets. Getting data from these Text-based logs does sometimes need some creativity. This is the case when trying to get an unknown amount of characters after the last instance of a specific character. To understand how to get to this, let’s work through three formulas, each time advancing the formula slightly to get to the end result.

RIGHT Function to Find Character in String from Right

Excel has a function named RIGHT it is defined as:
RIGHT – Returns the last character or characters in a text string, based on the number of characters you specify

RIGHT Formula breakdown:
=Right(Text, [Number of Character])

In the first example, the results bring back the data, but it shows an additional Space for the first result. On the third result, it then cuts off a part of the information required.

In the second example, the first result has the “=” part of the result and the Second result has a SPACE in front of it.

This formula in its basic form is only useful if the data structure is exactly the same every time.

LEN and FIND Functions to Extract Character in String from Right

The next two functions in Excel are LEN and FIND
LEN – Returns the number of characters in a text string

LEN Formula breakdown:
=LEN(Text)

FIND – locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string

FIND Formula breakdown:
=FIND(Find_Text,Within_Text)

Like all Excel Formulas, you can use other Formulas to obtain information and then bring back data, as long as the surrounding formula gets the data in its valid Specification. For example, if the Formula Expects a Numeric Value, you can not enter a Text Value.

Using LEN you can find the Length of the String. Using Find you can find the first instance of the Space ” ” Character and Deduct the two from one another.

This Formula can then be combined with the Original Formula and then bring back only the last characters. With some slight tweaking by removing the “= ” it brings back the correct result every time.

Here is a breakdown of the formula, starting from the innermost section and moving outward on how the formula is resolved.

Combined Functions to Find Character in String from Right

The last Excel function is SUBSTITUTE
SUBSTITUTE – Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string

SUBSTITUTE Formula breakdown:
=SUBSTITUTE(text, old_text, new_text, [instance_num])

This Formula does look complicated and the below can be copied and pasted for the use of a Space ” ” Character:

=RIGHT(B20,LEN(B20)-FIND("#",SUBSTITUTE(B20," ","#",LEN(B20)-LEN(SUBSTITUTE(B20," ","")))))

The Formula is broken up into the following process

• Take the Data and remove all instances of a Specific Character
• Find the Length of the Data that was just Modified
• Find the Lenght of the Original Data and Deduct the length of the Data where the Character was removed.

This above will give you the last instance number at which the Character exists

• Replace the Last Instance with a Character that will not be in the String
• Find the location of the Character of the string and Deduct it from the Original Length

Use the Original formula and get the right side of the String.

Here is a breakdown of the formula, starting from the innermost section and moving outward on how the formula is resolved.

An Example of the Formula as each part was explained in the above Breakdown