How to Extract 4- or 5-Digit Job Numbers from Excel Text
Question details
The user needs to extract a 4- or 5-digit job reference number from inconsistently formatted text strings in an Excel worksheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing text data from external accounting systems like Xero, where job references are embedded within irregular text strings instead of standard columns.
- Observed behavior
- Job numbers appear alongside varying prefixes or in different positions (e.g., JOB-1234, Job 12345, job no 1234), making standard extraction functions like LEFT, RIGHT, or MID ineffective.
Ensure you are using a spreadsheet version that supports dynamic array functions such as TEXTSPLIT, SEQUENCE, and LET, as older spreadsheet versions may not recognize these advanced formulas.
Use TEXTSPLIT and FILTER to Extract Numeric Sequences
This method dynamically splits the text to isolate numbers and filters for the one with the maximum length, which ensures leading zeros are preserved.
By utilizing the SEQUENCE function to generate digits 0 through 9, TEXTSPLIT can treat all numbers as delimiters to find the text components, or vice versa.
This formula splits the text around non-digit characters, calculates the length of each resulting numeric string, and returns the longest valid match.
Click on an empty cell adjacent to your target text (for example, cell B1 if your imported text is in A1).
Type the following formula into the formula bar: =LET(in,A1,numSets,TEXTSPLIT(in,TEXTSPLIT(in,SEQUENCE(10,,0),,1)),numSetLen,LEN(numSets),FILTER(numSets,numSetLen=MAX(numSetLen)))
Press Enter to extract the job number. Click and drag the fill handle at the bottom right of the cell to apply the formula to the rest of your data column.
Use TEXTSPLIT and MAX for Quick Numeric Extraction
If your job numbers do not start with zeros and are guaranteed to be the highest numeric value in the cell, you can use a shortened version of the formula.
Easily Manage Complex Formulas with WPS Spreadsheet
WPS Spreadsheet provides powerful data processing capabilities, supporting advanced formulas and dynamic arrays. It is an excellent, free tool for organizing messy imported data effortlessly.
- 1. Download WPS Office: Visit the official WPS website to download and install WPS Office for free.
- 2. Open your data file: Launch WPS Spreadsheet and open the file containing your exported Xero data.
- 3. Apply the extraction formula: Use the provided LET and TEXTSPLIT formulas directly in your worksheet to extract the job numbers accurately.
Frequently Asked Questions
Why do I get a #NAME? error when using the TEXTSPLIT formula?
The #NAME? error usually occurs if your spreadsheet software version is older and does not support the TEXTSPLIT or LET functions. Ensure you are using a modern, updated version of your spreadsheet software.
How can I extract the numbers if they always have a consistent prefix like 'JOB-'?
If the format is strictly consistent across all rows (e.g., always 'JOB-1234'), you can use a much simpler formula like =RIGHT(A1, LEN(A1)-4) or utilize the 'Text to Columns' tool with a hyphen as the delimiter.
What happens if the cell contains multiple numbers of the same length?
The FILTER formula provided returns the longest numeric string. If a cell contains multiple distinct numbers of the exact same maximum length, the formula will 'spill' and return all of them. To return just the first instance, wrap the formula in an INDEX function, like INDEX(..., 1).
Can I use Flash Fill instead of formulas to extract these numbers?
Yes, if the text pattern is somewhat recognizable, you can manually type the desired job number in the adjacent cell and press Ctrl+E to trigger Flash Fill. However, Flash Fill may struggle with highly irregular and inconsistent text structures, making formulas the more reliable choice.
