Excel data dumps are very great at combining lots of individual pieces of good information into one string. Unfortunately, this makes it very frustrating to the end user who then needs to extract that data. What if we could use formulas to do that dirty work for us…
No doubt you’ve received a data dump that includes an employees ID number, last name, first name, date of birth and dog’s name all in one string that looks like this: 589493CoughranNate01011986Fluffy. Good information but useless in its current form. Let’s learn how to extract that data.
=LEFT() allows you to extract a certain number of characters from a string, starting from the left-most character. This is useful when the number of characters will remain constant, like an employee number or zip code. The syntax for the formula is =LEFT(the cell you are extracting data from, number of characters you want to extract) as shown below:
=RIGHT() is the same exact concept except this will extract data starting from the right-most character. I will switch the Raw Data so the employee number is at the end:
One thing to watch out for! Since we are pulling a number from a text, the result will be text, and not a number. In other words, if I tried to sum the employee numbers, the result would be zero (0). If you require the extracted data to be a number, change your formula ever so slightly by multiplying it by 1 as shown below: