OmniDocExcel takes the first few characters: LEFT function extracts text
Use the LEFT function to extract a fixed number of characters from the beginning of the cell, and explain how to write formulas, downward filling, leading zeros and dates, and the counting rules for Chinese characters and spaces, which is suitable for extracting numbers, codes, and classification codes.
Key points of this article
- When the number of digits is fixed, use =LEFT(cell, number of characters) to directly get the first few digits, and the result is text. When the parameter is omitted, 1 character is taken by default.
- The leading zeros in the numerical code will be lost during storage. Set the cell to text format before inputting, otherwise the result obtained by LEFT will not be the original.
- If you use LEFT directly to get the serial number from the date cell, you should first use TEXT to convert it to the required format and then intercept it.
- LEFT is counted by characters, each Chinese character counts as 1, the first few Chinese characters and the first few letters can be taken in this way.
- Spaces at the beginning will be counted in the number of characters. You can use TRIM to remove them first and then intercept them.
When organizing data in a table, often only the first few characters of the cell are needed. The first three digits of the employee number may represent the department, the first two digits of the product code may represent the category, and the first six digits of the ID number may represent the region. Manually deleting rows one by one is slow and easy to miss. This kind of Excel function formula needs to take the first few digits, and the LEFT function can be used to complete it all at once.
This article is for those who deal with forms every day. After reading, you can write the LEFT formula, fill it in the entire column, and know what results will be obtained with numbers, dates, spaces, and Chinese characters. It is different from finding the mobile phone number from the text: the position of the mobile phone number is not fixed, you need to locate it first; the positions of the first few digits are fixed, just count them.
How to get the first few characters in Excel? One sentence conclusion
Use =LEFT(cell, number of characters) to take out the specified number of digits at the beginning, fill in the number of characters you want to keep, and the result is text. When the number of digits is fixed, write the number directly; when the position is determined by the delimiter, use it with FIND.
| your situation | Recommended practices | reason |
|---|---|---|
| The length of the first few digits of each cell is fixed, such as the first three digits of the number | =LEFT(A2,3) | The number of digits is fixed, just specify the number of characters directly |
| The encoding is purely numeric with leading zeros, such as 001 | First set the cell to text format, and then enter the data | The number format will lose the leading 0 and cannot be restored by LEFT. |
| The length of the first paragraph is not fixed, but there is a fixed symbol in front of it, such as "-" | =LEFT(A2,FIND("-",A2)-1) | Use FIND to find the symbol position, minus 1 which is the length of the previous segment |
| What is needed is the last few | Use the RIGHT function, such as =RIGHT(A2,4) | The direction is opposite and the parameters are written in the same way. |
Before you start: Confirm these points first
- Confirm whether the number of digits is fixed. Numbers can only be written directly if the number of digits is fixed. If the length of each line is different, but there is a uniform symbol in front of it, you must use FIND to locate it, otherwise the error will be obtained.
- See whether the cell stores text or numbers. Leading zeros are removed from digital storage, and dates are calculated as serial numbers. Both must be processed before taking the first few.
- Watch the leading space. Spaces also occupy one character. Data exported from network often has leading and trailing spaces. Direct interception will result in less or more data.
- Understand how characters are counted. LEFT is counted by characters, and Chinese characters, letters, and numbers are counted as one each. LEFTB is counted in bytes. Under double-byte language settings, Chinese characters usually occupy 2 bytes, and the results may be different.
- Make a backup first. When the formula result will overwrite the original data, make a copy of the original table first to avoid being unable to roll back after an operation error.
Use the LEFT function to extract the first few steps
- Prepare the data. Assume that column A is the product code, the first row is the header, and the data starts from A2, such as AB2024-0158.
- Write the header in the first row of the blank column next to it (such as column B), such as "first two digits", to facilitate subsequent verification.
- Enter =LEFT(A2,2) in cell B2 and press Enter. The result will display AB.
- Check whether the result of B2 is as expected against A2, and confirm that the number of digits and content are correct.
- Double-click the fill handle in the lower right corner of B2, or drag it to the last row to copy the formula to the entire column.
- If you need to save the results independently (for example, you want to delete the original column later), select column B and copy it, then use "Paste Special" to keep only the numerical values, and the formula will become a static result.
- If you need other digits, just modify the numbers in the formula. For example, if you want to get the first four digits, change 2 to 4.
Check each item after completion
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Result length | Randomly check a few lines and count the number of characters | If there are extra spaces in the result, use =LEFT(TRIM(A2),3) to remove the spaces first |
| Leading zeros | Check if the number still starts with 0 | Change the original data to text format and re-enter it, or set it uniformly before data entry. |
| date result | Compare the date shown on the original table | Use =LEFT(TEXT(A2,"yyyy-mm-dd"),4) to convert it to text first |
| exceeds length | When the number of characters is greater than the text length, the entire text will be returned without an error. | Check whether the original data is missing or whether the parameters are written incorrectly |
| blank cell | Results for empty rows should appear empty | Generally no processing is required; use IF judgment when unified display is required |
If you find that the results are not as expected during verification, first go back to column A to view the original content. Many problems come from hidden spaces or inconsistent formatting in the original data.
📊 Core comparison
Common interception methods in actual tables include the following. Which one you choose depends on whether the position is fixed:
| function | Where to start taking | Required parameters | Typical uses |
|---|---|---|---|
| LEFT | Take from the beginning | Number of characters | The first few digits of the number and the code prefix |
| RIGHT | Take from the end | Number of characters | The last few check digits and the last serial number |
| MID | Start from the specified position | Starting position, number of characters | The encoding of the fixed position in the middle |
| LEFT with FIND | before getting a certain symbol | Symbol position | The length of the front paragraph is not fixed, but there is a uniform separator in front of it. |
❓ Frequently Asked Questions (FAQ)
1. What happens if the number of characters of the LEFT function is omitted?
When the number of characters is omitted, Excel only takes 1 character by default. This default value often makes people mistakenly think that the formula is wrong. It is recommended to write down the number when writing the formula.
2. Can the results obtained by LEFT be summed directly?
uncertain. The result of LEFT is text, which is usually ignored by SUM. When calculation is needed, you can use VALUE conversion first, or add operations before the formula to convert the text into a numerical value and then calculate.
3. Can we only take the first few Chinese characters?
Can. LEFT counts by characters, and each Chinese character counts as 1, so =LEFT(A2,2) can take out the first two Chinese characters. You only need to pay attention to the different results of LEFTB if you need to calculate by bytes in a double-byte language setting.
4. After dragging the formula to the end, some rows have incorrect results. How can I check?
First filter out the rows with abnormal results and compare them with the original content. Common reasons are spaces at the beginning, inconsistent number formats, or different delimiter positions in some rows. After correcting the original data, the formula will automatically update the results.
📝 Summary
LEFT is a direct way to deal with the need to get the first digit of Excel function formula: write the number when the digits are fixed, cooperate with FIND when the position changes with the separator, and then pay attention to the details of numbers, dates and spaces, and you can stably complete the extraction of the entire column.
