OmniDocHow to split text content with Excel formula
Instead of using the column splitting function, use text functions such as LEFT, MID, and FIND to split the text in the cell into multiple columns according to delimiters. Explain how to write formulas, how to handle when the number of paragraphs is not fixed, and how to check the results.
Key points of this article
- When the delimiter is fixed and the source data will be updated, it is more appropriate to use formulas rather than splits, and the results will be automatically recalculated along with the source cells.
- Use FIND to locate the separator position, and then use LEFT and MID to extract each paragraph. This is the basis for splitting text.
- When the number of segments is not fixed, you can use SUBSTITUTE to replace the separator with a large number of spaces, and then use MID to intercept according to the position.
- After writing the formula, fill it down and check it randomly, focusing on error values, extra spaces and number formats.
In Excel, you often encounter a cell with several messages, such as "Zhang San-sales Department-Beijing", or "order number | customer | amount" derived from the system. To divide them into different columns, many people subconsciously use the "separate" function. The sorting operation is intuitive, but it is one-off: as soon as the source data is changed, the results have to be redone.
This article is for people who need to organize such forms on a regular basis, especially if the data is still updated or the delimiters are not completely uniform. After reading, you will know how to use FIND, LEFT, MID and other text functions to separate the text, how to write the formula when the number of segments is not fixed, and how to check whether the results are correct.
The sample data in this article are fictitious and you can replace them with your own tables.
Do you want to use formulas to split cell text? One sentence conclusion
One sentence conclusion: use formulas when the separators are fixed and the source data will change; it is faster to use splitting only once and the data will not change after that.
| your situation | Recommended practices | reason |
|---|---|---|
| One-time sorting, the source data will not be changed afterwards | sort | It can be done in a few steps without having to write a formula. |
| The source data will be updated regularly, and it is hoped that the results will automatically change accordingly. | text function formula | The formula refers to the source cell. If the source data is changed, the result will be recalculated. |
| The delimiter is uniform and the number of segments is fixed (e.g. two "-") | FIND plus MID or LEFT, RIGHT | The position can be calculated directly, and the formula is short and easy to check. |
| The number of paragraphs is not fixed, or the position of the separators is uneven. | SUBSTITUTE plus MID plus TRIM | Replace each delimiter with a space that is wide enough, and take segments by position, regardless of the number of segments. |
| Use a newer version of Excel 365 | TEXTSPLIT, TEXTBEFORE, TEXTAFTER | The function returns the result directly according to the delimiter, which is more concisely written, but the old version cannot be used. |
Before you start: Confirm these points first
- What is the separator and whether it is uniform. Read the first dozen lines first to confirm whether the separator is "-", "/", a space or other symbols. The Chinese comma "," and the English comma "," are two different characters in the formula. When they are mixed, the separator will not be found.
- There are no spaces on both sides of the delimiter. Some source data has spaces on both sides of the delimiter, and the extracted results will have spaces following them, which need to be cleaned with TRIM.
- Whether the target column is empty. The formula results will be spread to the right. If there is content on the right, it will be overwritten. Leave enough columns before you start.
- Do you need to calculate the extracted numbers later? The result obtained by the formula is text by default. Direct summation will get wrong results and needs to be converted into a numerical value using VALUE.
- Back up original data first. Make a copy of the worksheet before splitting for easy reference when problems arise.
Steps to split text by delimiter using FIND and MID in Excel
The following takes the content of cell A2 as "Zhang San-Sales Department-Beijing" as an example. The goal is to put the name, department, and city into columns B, C, and D respectively.
- Confirm the source data location. Enter "Zhang San-Sales Department-Beijing" in A2, or select the source data cell in your own table.
- Enter the formula for segment 1 in B2: =LEFT(A2,FIND("-",A2)-1). FIND finds the position of the first "-" and subtracts 1 to be the number of words in the previous paragraph. LEFT takes out so many words from the left to get "Zhang San".
- Enter the formula for the middle segment in C2: =MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1). MID is taken from the end of the first "-", and the length is equal to the number of words between the two "-" to get "Sales Department".
- Enter the formula to get the last segment in D2: =MID(A2,FIND("-",A2,FIND("-",A2)+1)+1,LEN(A2)). It starts from behind the second "-", and there is no problem if the length exceeds the actual length. The MID will not go past the end of the string and get "Beijing".
- Select B2 to D2 at the same time, double-click or drag the fill handle downward to apply the formula to the entire column.
- If the number of segments is not fixed, change B2 to =TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",100)),1,100)). SUBSTITUTE replaces each "-" with 100 spaces, and each segment is separated into fixed-width blocks; MID takes segments according to the starting position, the starting position of the 2nd segment is changed to 101, the 3rd segment is changed to 201, and TRIM removes excess spaces. The number of words in each paragraph must be less than 100. If it exceeds, increase the number by 100.
- Check the results line by line to confirm that there are no incorrect values, blank lines, or misplaced content, and then decide whether to retain the formula.
- If you need to fix the results so that they no longer change with the source data, copy these cells and use the "Value" option in "Paste Special" to overwrite them.
Check each item after completion
| Check item | What do you think? | What to do if there is a problem |
|---|---|---|
| Is the number of segments correct? | Randomly check 5 lines, count the delimiters in the source data, and compare the number of columns with the results | Lines with incorrect number of segments are checked separately, mostly because the delimiter is not uniform or there is one more symbol. |
| Error value | Visually check to see if # VALUE appears! Or # NCMA | Indicates that the delimiter cannot be found in the line and wraps the formula with IFERROR, for example, = IFERROR (LEFT (A _ 2 ~ find ("-", A _ 2)-1), ") |
| Extra space | See if there are spaces at both ends of the result, or compare the word count with LEN | Apply TRIM to the outer layer of the formula |
| Can the numbers be calculated? | Sum the detached number column to see if the result is reasonable | Convert with VALUE, or precede the formula with two minus signs |
| Whether the number of rows is complete | Compare the number of non-empty rows between the source column and the result column | A cell that is not filled to the end, drag and fill the handle |
| Is the linkage effective? | Modify a row in the source table to see if the results change synchronously | If it remains the same, check whether it has been pasted into a numeric value, or if the calculation mode is set to manual |
# frequently asked questions (FAQ)
1. The separator is a mixture of Chinese and English commas, how to deal with it?
First use SUBSTITUTE to replace Chinese commas with English commas, such as = SUBSTITUTE (A2, "), and then use this result as the source data in the split formula. The replaced content is only used for calculation, and the original data can remain unchanged.
2. The split result appears # VALUE! What is it caused by?
In most cases, the delimiter cannot be found on that line, often because of a mistyped symbol, an inconsistent full-width half-width, or no delimiter on that line at all. First locate the wrong row, check the source data, and then use IFERROR to wrap the formula, so that the blank row is displayed as blank to facilitate subsequent screening.
3. Can you write the split result directly back to the original cell?
This is not recommended. The formula refers to the original cell, the original text will be lost after overwriting, and the formula may make an error by referencing itself. It is safer to put the results in the new column, check it correctly, and then paste it to the desired location in a "numerical" way.
4. What about the old version of Excel without TEXTSPLIT?
There is no need to upgrade. FIND, LEFT, MID, SUBSTITUTE and TRIM used in this article are available in each version, and the split can be completed by combining them step by step. It is only in the new version that it is appropriate to use newer functions such as TEXTSPLIT directly.
# Summary
When the separator is uniform and needs to be updated with the source data, it is safe to use FIND with LEFT and MID to write the formula; when the number of segments is not fixed, use the combination of SUBSTITUTE, MID and TRIM instead. After the formula is written, remember to spot-check the number of segments, error values and spaces, confirm that the results are correct and then fix the results.
