Excel - Extract Certain Characters from Text String
As accountants, we constantly deal with the general ledger. Most of the general ledger (GL) reports we run either from Oracle, Microsoft AX, or Quickbooks contain long text strings.
For example, if I have a company account 122, subsidiary 15, product line 10, general ledger account 15500, and sub-ledger/other random number of 2550 my text string would look like 122-15-10-15500-2550.
That text string is extremely annoying especially if all I want to do is sort in excel by a particular general ledger account. Say I wanted to sort by GL#15500, but my beginning strings are all different. For example, I have company account 122, 133, 145, and 166. It would be difficult to sort just by my general ledger account 15500.
What is your solution to extracting numbers from text strings?
Try the following formula =MID(text, start_num, num_chars).
1) For "text", select the cell where your text resides. The one you want to pull the number from.
2) "start_num" refers to where you want the pulled number to start from left to right. So if my text string is 122-15-10-15500-2550. The 11th text (including dashes) is my general ledger account.
3) "num_chars" refers to how many characters I want to pull beginning from my "start_num". In this case it would be 5.
My formula for the above text string would be =MID(H15,11,5). The result is 15500.
What if I wanted to just pull text from the right or the left?
Use =LEFT(text, num_chars) or =RIGHT(text, num_chars).
This will pull the number of characters beginning from the left for LEFT and right for RIGHT.