'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Split Numbers and Units into Separate Excel Columns
Question details
The user needs to separate combined values consisting of numbers and text units (e.g., '5kg') into two distinct columns when there is no delimiter or space between them.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing and cleaning imported dataset where numerical values and measurement units are combined in single cells.
- Observed behavior
- Numbers and units are merged together in one cell without spaces, preventing mathematical operations on the numerical values.
Ensure you have inserted two blank columns directly next to your original data column to accommodate the separated numbers and text units without overwriting existing data.
Use Flash Fill to Split Numbers and Units Automatically
Flash Fill is the fastest and most efficient method to extract numbers and text patterns automatically without using complex formulas.
Excel's Flash Fill feature can detect patterns in your manual data entry and automatically fill the rest of the column based on that pattern. This works perfectly for extracting numbers and units.
In the first adjacent blank cell, manually type just the number part (e.g., '5') from your original mixed cell and press Enter.
Select the cell you just typed in, go to the 'Data' tab on the ribbon, and click 'Flash Fill', or simply press the keyboard shortcut Ctrl+E. Excel will extract all numbers down the column.
In the next blank column, manually type the text unit part (e.g., 'kg') for the first row and press Enter.
Select that cell and use 'Flash Fill' (Ctrl+E) again. Excel will infer the pattern and automatically fill the units for the remaining rows.
Apply Custom Format to Display Units Without Altering Values
If you only need a single consistent unit across all cells and want to keep the data calculable, applying a custom number format is highly recommended.
Use Formulas to Extract Numbers and Text
Use text-extraction formulas if your data updates dynamically and you need the split to happen automatically as new data is entered.
Split Mixed Data Effortlessly with WPS Spreadsheet
WPS Spreadsheet offers powerful data extraction tools, including intelligent Flash Fill and advanced formulas, allowing you to split combined numbers and text instantly. It provides a familiar, fast interface fully compatible with Excel files.
- 1. Open File: Open your dataset containing the mixed numbers and units in WPS Spreadsheet.
- 2. Provide an Example: In the adjacent blank column, manually type the extracted number for the first row.
- 3. Use Flash Fill: Navigate to the Data tab and click Flash Fill, or simply press Ctrl+E to auto-fill the numbers.
- 4. Extract Units: Repeat the exact same process in the next column to effortlessly extract the text units.
Frequently Asked Questions
Why is Flash Fill not working correctly for my mixed data?
Flash Fill relies on recognizing consistent patterns. If your data formatting is highly irregular (e.g., mixing spaces, varying unit text lengths, or placing text before numbers randomly), Flash Fill might make mistakes. Try providing two or three manual examples before using the tool to improve its pattern recognition.
Can I split numbers and units using Text to Columns?
Text to Columns works best when there is a delimiter, such as a space or comma, separating the number and the unit. If there is no space (e.g., '10lbs'), Text to Columns cannot automatically split them using the Delimited option. You can use the 'Fixed Width' option, but only if all numbers in your column have the exact same number of digits.
How do I calculate cells that already contain text units?
Spreadsheet software cannot perform mathematical operations on cells containing mixed text and numbers. You must either split them into separate columns using Flash Fill, or strip the text completely and apply a Custom Number Format to display the unit visually while keeping the underlying value as a calculable number.
Is there a simple formula to remove letters from numbers?
Removing letters from numbers using formulas usually involves complex text and sequence functions, especially if the text length varies. While newer spreadsheet versions have advanced functions for this, Flash Fill (Ctrl+E) remains the most straightforward and beginner-friendly method for stripping letters from numbers.
