In this lesson, we’re going to extract data from a string using Text Left Function, Text Right Function and Text Mid Function.
We’ll be using ISBN number from a table of books record to extract information into separate columns.
Anatomy of ISBN Number is as follows.
|Digits 1-3||EAN (European Article Number)|
|Digit 4||Group Identifier (country or language code) 0 or 1 = English, 2 = French, 3 = German, 4 = Japanese, 5 = Russian, 7 = Chinese|
|Digits 5-12||publisher prefix and title identifier|
|Digit 13||Check digit (proves accuracy)|
You need to open the worksheet containing text you want to extract information from.
We are going to extract EAN Number, Group Number, Title and Check digit in separate columns.
You need to select Focus on EAN box. Go to the “Formulas” tab in ribbon box. And explore “Text” Formulas.
Select “Left” function from Text Formulas.
This function will ask for two arguments.
One is “Text” from you need to extract information. And “Num_Chars” Number characters, you need to get the characters from the text.
As ISBN’s Anatomy describes the 3digits from left as EAN. We’ll pass the Text box address (I.e.: E4) and “3” Num_chars to get three digits from the E4 String.
Now Focus on Digit Column. Select “Right” Function from Text Formulas. It’ll again ask for two arguments. “Text” and “Num_chars” that are the same as in Left function. You need to pass reference of string and characters to get in the box.
After that, focus on group. Select “Mid” functions from Text Formulas. It’ll ask for three function arguments.
- “Text” is a reference to string from data needs to be extracted.
- “Start_num” is the number of letters to start Extraction from.
- “Num_chars” is the number of letters to extract.
We’ll be passing “E4” in “text”, “5” in the “Start_num” and “1” in the “Num_chars”. It’ll extract the Group identifier for the country or language.
Again we’ll be using mid function as we need to get the text from the mid of the string. Focus on “Title” column. Select Mid Function from the text Formulas. And this time we’ll fetch the data from “text” “E4”, “start_num” from “6” and “Num_chars” “8”. It’ll return the Title Identifier from ISBN.
Copy the for column boxes with the formulas. And paste them in below rows with ISBN number to extract the data from ISBN.