How to extract certain text from a cell in Excel
By
SpreadCheaters
You can watch a video tutorial here.
Excel has several text functions that can be used to manipulate text. Text extracted from a cell is known as a Substring. When working with data, you may need to extract some text from a cell. In this example, we will look at extracting substrings from store codes in which the first 2 characters indicate the state and the last 3 digits are the store number.
Step 1 – Extract text from the left
– Go to the first cell of the destination column
– Enter the formula to get 2 characters from the left:
=left(<cell reference>,2)
Step 2 – Extract text from the right
– Go to the first cell of the destination column
– Enter the formula to get 3 characters from the right:
=right(<cell reference>,3)
Step 3 – Copy the formula
– Using the fill handle from the first cell, drag the formula to the remaining cells
OR
a) Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
b) Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)
