How to Extract Item Numbers with Nonstandard Spaces in Excel
Question details
The user needs to correctly extract item numbers separated by spaces, but formula extraction and Flash Fill fail due to hidden nonstandard spaces.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Attempting to extract substrings from a cell using formulas like LEFT, MID, or SEARCH based on space characters.
- Observed behavior
- Formulas return a #VALUE! error because the delimiter is actually a nonstandard Unicode space rather than a standard ASCII space, which also causes Flash Fill to split the values incorrectly.
Ensure your cell formatting is set to 'General' rather than 'Text' before typing formulas, and check if your system's regional settings require semicolons (;) instead of commas (,) to separate function arguments.
Identify and Extract Using UNICHAR and FIND
Determine the exact Unicode character representing the nonstandard space, and update your extraction formula to look for that specific character.
Often, data imported from web pages or other databases contains non-breaking spaces (character 160) instead of regular spaces (character 32). Standard FIND or SEARCH functions will fail to locate them.
In a blank cell, enter the formula =UNICODE(MID(A2, 7, 1)) where '7' is the position of the space in your text. Press Enter. If it returns 160, you are dealing with a non-breaking space.
To extract characters before the space, use the formula =LEFT(A2, FIND(UNICHAR(160), A2) - 1). This dynamically finds the non-breaking space and pulls the preceding text.
To extract the characters after the space, use the formula =MID(A2, FIND(UNICHAR(160), A2) + 1, 99). This pulls up to 99 characters following the non-breaking space.
Normalize Data Using the SUBSTITUTE Function
Replace the problematic Unicode spaces with regular spaces first, allowing your standard string manipulation formulas or Flash Fill to work normally.
Clean and Extract Data Seamlessly with WPS Spreadsheet
WPS Spreadsheet provides full support for advanced text-processing functions like UNICODE, UNICHAR, and SUBSTITUTE, enabling you to clean problematic imported data with ease.
- 1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your nonstandard spaced data.
- 2. Apply the cleaning formula: In an adjacent cell, enter =SUBSTITUTE(A2, CHAR(160), " ") to easily normalize the spaces.
- 3. Extract the normalized data: Use standard LEFT, RIGHT, or MID formulas on the cleaned cell without running into #VALUE! errors.
- 4. Drag to apply: Double-click or drag the fill handle downwards to process the rest of your column instantly.
Frequently Asked Questions
Why does my text extraction formula return a #VALUE! error?
A #VALUE! error occurs when the FIND or SEARCH function cannot locate the target text. If you specified a regular space (" "), but the cell actually contains a non-breaking space (Unicode 160) or another nonstandard character, Excel will not find it and returns an error.
How do I find out what hidden character is in my cell?
You can isolate the character and identify it using the formula =UNICODE(MID(A1, position, 1)). Replace 'A1' with your cell reference and 'position' with the numerical position of the strange character. This will return the numeric code of that character.
Why does my formula display as plain text instead of calculating?
If the formula itself is showing in the cell, the cell's format is likely set to 'Text'. Change the format to 'General' in the Home tab, then double-click inside the cell and press Enter to trigger the calculation.
Can I use Flash Fill instead of formulas to split these items?
Flash Fill often struggles to recognize patterns involving nonstandard Unicode spaces. It is best to first normalize the spaces to regular spaces using the SUBSTITUTE function. Once normalized, Flash Fill will be able to split the values accurately.
