OmniDocHow to use Excel text functions: How to extract numbers and text
Explain the general extraction idea of Excel text functions: first use FIND to locate the separator, and then use LEFT, MID, and RIGHT to intercept. Attached is the practice of separating text in brackets, length, width and height, numbers and letters, and when to use column splitting or quick filling instead.
Key points of this article
- Find the rules before extracting content: whether the delimiters are uniform and the positions are fixed, decide which function combination to use.
- FIND returns the character position, MID uses this position to intercept, and the two can be used to extract the text within the brackets.
- For data such as length, width and height, you can use FIND twice to find two separators, and then intercept three segments of numbers respectively.
- LEFT and MID return text, which must be converted into numbers using -- or VALUE before participating in calculations.
- When the rules are not uniform, it is easier to divide the columns or fill in quickly than writing long formulas, and it is easier to check.
In Excel, we often encounter situations where multiple pieces of information are stuffed into one cell: the order number and model are connected together, the specification is written as "120×80×60", and only the text in brackets is useful in the remarks. Copying and pasting them one by one is slow and error-prone. Text functions are prepared for this type of splitting, commonly used ones are LEFT, RIGHT, MID, FIND and LEN.
After reading this article, you can follow the idea of "find the position first, then intercept" and write your own formulas for extracting text in brackets, splitting length, width and height, separating numbers and English letters, and judge when you should switch to column splitting or quick filling.
How to use Excel text functions to extract numbers and text? One sentence conclusion
As long as the position of the target content can be located using delimiters or character numbers, first use FIND to find the position, and then use LEFT, MID or RIGHT to intercept it; when the position is not fixed and you are too lazy to write a formula, use column splitting or quick filling instead.
| your situation | Recommended practices | reason |
|---|---|---|
| The content is at the beginning and the length is fixed (for example, the first 4 digits are the year) | LEFT takes the first few digits | Direct interception, short formula |
| The content is at the end and the length is fixed (for example, the last 2 digits are the unit number) | RIGHT takes the last few digits | Take it from the right side, no need to find the location |
| The content is in the middle, preceded and followed by symbols such as brackets, horizontal bars or × | FIND positioning plus MID interception | Symbol positions can be calculated and adapted to length changes |
| The structure of each row is the same, I just want to disassemble it at once | Split columns (by delimiter or fixed width) | No formulas are required, the results can be used directly |
Before you start: Confirm these points first
- Take a look at the rules. Randomly read about 10 lines to confirm whether the delimiters are uniform. Some data are written in half-width brackets "(", and some are written in full-width brackets "(", so FIND will not be able to find it.
- Confirm the case. FIND is case-sensitive, "x" and "X" will be treated as different characters. When distinction is not needed, SEARCH can be used instead.
- Check spaces. The extra spaces before and after will offset the LEN and position calculations. Use TRIM to clean them up if necessary.
- Clear the result type. LEFT, MID, RIGHT return text, even if the content looks like numbers. The subsequent sum or multiplication must be converted first.
- Keep the original data. Write the formula in a blank column. Do not directly overwrite the original column. You can correct it if you make any mistakes.
Extract the text in brackets step by step
Assume that the content of cell A2 is "Bluetooth headset (black)", and you need to remove the "black" in the brackets.
- Enter =FIND("(",A2) in B2 to get the position of the left bracket. The result in this example is 5 because "Bluetooth headset" occupies the first 4 characters.
- Enter =FIND(")",A2) in C2 to get the position of the right bracket. In this case the result is 8.
- Enter =MID(A2,B2+1,C2-B2-1) in D2. It means starting from the character after the left bracket and taking out the number of characters in the middle. In this case, it is 2 characters. The result is "black".
- After you are familiar with it, you can combine the three steps into one formula: =MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1).
- If the brackets are full-width, first use SUBSTITUTE to unify the symbols. For example, change the formula to =MID(SUBSTITUTE(A2,"(","("),FIND("(",SUBSTITUTE(A2,"(","("))), or first replace the full-width brackets with half-width brackets in the blank column.
- Fill down to the entire column. The appearance of #VALUE! usually means that there are no parentheses in this line. After checking, you can decide whether to wrap it with IFERROR.
How to dismantle other common situations
- Length, width and height "120×80×60". The first paragraph uses =LEFT(A2,FIND("×",A2)-1); the second paragraph uses =MID(A2,FIND("×",A2)+1,FIND("×",A2,FIND("×",A2)+1)-FIND("×",A2)-1); the third paragraph uses =RIGHT(A2,LEN(A2)-FIND("×",A2,FIND("×",A2)+1)). If the original data is separated by the letter x, just replace the symbol in the quotation marks with x.
- Numbers and English letters are mixed, and the positions are fixed. If letters are always 2 digits, use LEFT(A2,2) to get letters and MID(A2,3,99) to get numbers.
- Numbers and English letters are mixed, and the position is not fixed. Writing formulas line by line is difficult to maintain. It is recommended to manually enter the first result next to it, and then use quick fill (commonly used shortcut keys Ctrl+E in Windows) to let Excel recognize the pattern.
- Data comes from PDF table. You can first use the PDF editor of the OmniDoc desktop client to export to Excel, and then split it using the above function. If the original document is a scan, the OCR recognition results must be checked item by item and do not be used directly.
Check each item after completion
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| error value | Does #VALUE!, #N/A appear? | Most of them are missing delimiters. Filter out these lines and check them one by one. |
| Is the length reasonable? | Use LEN to compare with the original text | Check if there are any extra spaces and clean them with TRIM |
| Can numbers be calculated? | Sum the results to see if an error is reported or the result is 0 | Use -- or VALUE to convert, remove the unit and then calculate |
| Sampling control | Randomly select five lines and compare them word by word with the original text | When the law is not uniform, separate or manual correction will be used instead. |
| Is the symbol unified? | Check whether parentheses, x and x are mixed | Recalculate after unifying symbols with SUBSTITUTE |
# Core comparison
| Method | Appropriate data | Advantages | Limitation |
|---|---|---|---|
| LEFT, RIGHT | Fixed position and length | The formula is short and easy to understand. | When the length changes, it will make a mistake. |
| FIND plus MID | There are fixed symbols before and after. | Can adapt to the change of length | Clean up when the symbols are not unified. |
| Separate | Uniform delimiter or uniform width | Don't write the formula, do it at once. | The result is fixed and the data update needs to be redone. |
| Fast filling | There is an obvious rule, and the number of rows is small. | No formula is needed. | Misjudgment may be made when the law is unstable. |
# frequently asked questions (FAQ)
1. What's the difference between FIND and SEARCH?
FIND is case sensitive and does not support wildcards. SEARCH is case-insensitive and supports the wildcard "?" And "*". Use FIND when you need to position accurately by case, otherwise it is safer to use SEARCH in most cases.
2. How much should I write in the third parameter of MID?
It is written about the number of characters to be taken out. When the length is uncertain, you can write a number that is large enough, for example, 9999MID will be fetched to the end of the text.
3. Why can't the extracted numbers be summed up?
The common reason is that numbers are stored as text, and there may be a green triangle prompt in the upper-left corner of the cell, or an invisible space before and after it. You can use-- transform, convert with VALUE, or clean up with TRIM first.
4. Is there a formula that can handle all mixed data?
Usually not. When the positions of numbers and words are changeable, multiple functions need to be combined, and the formula will be very long and difficult to maintain later. A more practical approach is to first sort by a uniform delimiter, and then check the remaining small number of special lines manually or process them with fast padding.
# Summary
When extracting data, first look at the law, and then select the function: fixed position with LEFT or RIGHT, before and after the symbol with FIND with MID, the law is not uniform, it is given to separate or fast filling. Checking item by item according to the checklist in this article, especially the numerical format and sampling comparison, can make fewer detours in how to use the excel text function.
