OmniDocExcel formula method for extracting numbers and text
Numbers and text are mixed together in the cell. How can I use formulas to extract the numbers or text separately? This article explains clearly the judgment ideas, formulas that can be directly applied, applicable scenarios for quick filling and sorting, and how to check item by item after extraction.
Key points of this article
- When the content length is not fixed, digit-based interception methods such as LEFT and RIGHT are unreliable, and character-by-character judgment formulas should be used instead.
- Using TEXTJOIN with MID and ISNUMBER, you can splice all the numbers in a cell, or just keep the text.
- When the result is a number in text format, use VALUE or add 0 to convert it before summing, otherwise statistics will be missed.
- Use quick fill first for regular text, use column for content with fixed format and divided by symbols, and leave formulas for complex content.
- After extraction, sample the original text to check whether the decimal point, negative sign and thousandth comma are missing.
Many tables contain both numbers and text in one cell, such as "Order A123, 45 pieces in total, shipped", or "Zhang San 13800001111 paid". It is necessary to extract the numbers separately for statistics, or leave only the text for classification. Manually copying one by one is time-consuming and error-prone. This article is written for people who often organize tables. It talks about how to use formulas to extract numbers or text when the content length is not fixed and the position is irregular.
After reading, you can get three things: a judgment idea, two formulas that can be directly reused, and an extracted checklist. The formula in this article takes cell A1 as an example. Just replace it with your own cell address when using it.
How to extract numbers or text from mixed content? Conclusion in one sentence: judge character by character, and then put together the characters that meet the conditions.
Functions such as LEFT, RIGHT, and MID are suitable for content with fixed digits, such as the first 6 digits of an ID number. The number position and length of mixed text may be different in each line, and truncation by digits will miss or overtake. A safer idea is to split the cell into individual characters, determine whether it is a number one by one, and finally put the qualified characters back together in the original order.
| your situation | Recommended practices | reason |
|---|---|---|
| Numbers and text are mixed, the number is not fixed, and the Excel version is newer | Array formula using TEXTJOIN plus MID and ISNUMBER | One formula can extract all the numbers, and the results will automatically change according to the content. |
| Same situation but older version without TEXTJOIN | Use auxiliary columns to separate them word by word, and then use & to connect | Don’t rely on new functions, you can see the intermediate results at every step |
| The text has a fixed pattern, such as the format "Quantity: 12" | Quick fill (common shortcut keys Ctrl+E) | Just demonstrate one or two results, no need to write formulas |
| There is a fixed symbol separation between numbers and text | Split into columns, split by delimiter | Intuitive operation, suitable for processing the entire column of data at once |
| The data is in PDF and cannot be copied directly | First use OmniDoc's PDF editor to OCR and export to Excel, then apply the above method | The text in the scan needs to be recognized into table content first |
Before you start: Confirm these points first
Let’s first look at what the data looks like. Open a few lines of real data and pay attention to whether the numbers have decimal points, commas or negative signs, and whether there are letters and symbols in the text. These details determine whether the formula should be changed or not. If you ignore them, errors will occur quietly.
Confirm Excel version. TEXTJOIN requires a newer version. When you are unsure, enter =TEXTJOIN(",",TRUE,"a","b") in a blank cell. If you can calculate a and b, it means it can be used. Otherwise, follow the auxiliary column method below.
Think about where to put the results. It is recommended to put the results in a new column and do not overwrite the original data. The original content is retained, and if the formula is written incorrectly, it can be modified.
Distinguish between "getting numbers" and "getting values". The numbers extracted by the formula are usually in text format. They look like numbers, but they cannot be summed directly. This point will be reminded again in the later verification.
Multiple rows of data must be in a unified format. If some lines use full-width brackets and some use half-width brackets, the result of character-by-character judgment may be inconsistent. First use search and replace unified symbols to reduce rework.
Steps to extract numbers from mixed cells in Excel
- Enter the extraction formula in the first row of column B (or any blank column), for example =TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:50")),1)),MID(A1,ROW(INDIRECT("1:50")),1),"")).
- INDIRECT("1:50") in the formula means that only the first 50 characters are checked. If the cell content is longer, change 50 to a larger value.
- If you need three-key confirmation, press Ctrl+Shift+Enter; for new versions of Excel, just press Enter.
- Drag the fill handle downward, copy the formula to other rows, and check whether the result matches the original text row by row.
- If the number contains a decimal point, change the condition to IF(OR(ISNUMBER(--MID(A1,ROW(INDIRECT("1:50")),1)),MID(A1,ROW(INDIRECT("1:50")),1)="."),...), otherwise the decimal point will be removed.
- For the results that need to be calculated, select the column, use "split columns" or add 0 in front to convert the text into a numerical value, and then add the sum.
- If the original text is in PDF, first use the PDF editor of the OmniDoc desktop client to perform OCR on the scanned document, then export it to Excel, paste the resulting text into column A, and then perform the above steps.
Extract text using the same method: remove the numbers and keep only the text
To retain only text, reverse the judgment condition: only characters that are not numbers are retained. The formula is =TEXTJOIN("",TRUE,IF(ISERROR(--MID(A1,ROW(INDIRECT("1:50")),1)),MID(A1,ROW(INDIRECT("1:50")),1),"")).
This formula preserves spaces, punctuation, and symbols. If you only want pure Chinese characters or letters, you can use this formula to extract them first, and then use SUBSTITUTE to replace the extra symbols with empty characters. For example, if you remove the spaces for content like "paid" and then classify it, the result will be neater.
Check each item after completion
After the extraction is complete, do not use the results directly, check them one by one according to the list below.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Are the numbers complete? | Extract 5 to 10 lines and compare them with the original text to see if the digits are consistent. | Check if the range exceeds 50 characters, expand and refill |
| Decimal point and negative sign | Whether lines with "-" or "." in the original text should be retained | Follow step 5 to modify the judgment conditions and include the decimal point and negative sign in the retention range. |
| Can you participate in the calculation? | Use SUM to sum the results and see if it is 0 or report an error | When the result is text, use column separation or multiply by 1 to convert it to a numerical value. |
| Whether the text is mixed with residual symbols | Check whether there are any extra spaces or punctuation in the text results | Use SUBSTITUTE to replace the symbols that need to be removed |
| Is the scanned document recognized correctly? | Check key figures against original PDF image | Key contents such as numbers and amounts must be manually checked one by one. The OCR results cannot be directly used as official data. |
📊 Core comparison: How to choose between formula, quick filling and column sorting?
Each of the three methods has its own appropriate circumstances. When the content is complex and needs to be updated automatically as the data changes, use formulas; when the pattern is obvious and it is only processed once, use quick fill; when there are fixed separators and you want to cut into multiple columns at one time, use split columns.
| method | Suitable situation | advantage | limitations |
|---|---|---|---|
| Official (TEXTJOIN, MID, ISNUMBER) | Numbers and text are mixed, and the position and length are not fixed. | Flexible and reusable, the results will change after the data is updated | There is a certain learning cost in writing, and older versions of Excel need to be replaced with auxiliary columns. |
| Quick fill (Ctrl+E) | There are obvious patterns in the text, such as "Quantity: 12" | Get started quickly, just demonstrate one or two results | Depends on rules, the results may be inaccurate if the format is confusing, and need to be checked line by line. |
| sort | There are fixed separators such as spaces, commas, and horizontal lines between numbers and text. | Intuitive operation, the entire column can be cut in one go | Mixed text without delimiters cannot be processed |
🎯 Which method is suitable for you?
If you have to deal with the same kind of tables every week, the content format is relatively stable, and it saves effort to write the formulas into the template. If you only process a piece of data occasionally and the pattern is obvious, you can quickly fill in the data once and demonstrate it once. If the data itself is separated by symbols, just use column sorting and it can be completed in a few seconds.
❓ Frequently Asked Questions (FAQ)
1. Why can’t the extracted numbers be summed?
The numbers extracted by the formula are usually in text format, and there may be a green triangle prompt in the upper left corner of the cell. Select the column and use "Split Column" to complete directly, or enter 1 in a blank cell and copy it, and then use the "multiply" operation of Paste Special to convert the text into a numerical value.
2. How to extract numbers from text and only take the first number?
Character-by-character extraction will put all the numbers together. As long as there is only the first number, you need to find the position of the first number first, and then intercept it from that position, which is a cumbersome process. When the content is regular, it is usually easier to use quick fill to demonstrate a result.
3. What should I do if the old version of Excel does not have TEXTJOIN?
In the auxiliary column, use MID to split each character into a separate cell. Use ISNUMBER to judge each cell. Keep the ones that meet the conditions and leave them blank if they do not. Finally, use & to connect the columns. There are a few more steps, but you can see the results of each step for easy inspection.
4. If the data is in PDF, can these formulas be used directly?
The text in the PDF needs to be converted into Excel cell content first. When the text can be copied, just paste it directly; the scanned document needs to be OCR recognized first. The PDF editor of the OmniDoc desktop client supports OCR and can be exported to Excel. After exporting, apply the above method. The identification results still need to be checked against the original.
📝 Summary
When numbers and text are mixed together in a table, intercepting by digits is often not enough. It is usually more reliable to judge character by character and then splice them. Only when you first see the data format clearly, choose the right formula or quickly fill in and sort the data, and then check with samples after extraction, will the result of extracting numbers from the table be reliable.
