Function Problems

How to Convert Text with Number and Decimal-Place Count to Decimal in Excel

Partner Editor Sep 28, 2026 869 views

Question details

The user needs to convert a text string that contains a base number and a decimal-place count separated by a delimiter (e.g., '21730 | 8') into a valid decimal number.

How to Convert Text with Number and Decimal-Place Count to Decimal in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Data cleaning and transformation where numerical values and their decimal placement rules are combined in a single text cell, requiring extraction and mathematical calculation.
Observed behavior: Attempting to split the text using just a space as the delimiter resulted in an #N/A error. Using the vertical bar delimiter with TRIM and TEXTBEFORE/TEXTAFTER correctly returned the expected decimal value.

Before you start

Verify that your version of Excel or WPS Spreadsheet supports the TEXTBEFORE and TEXTAFTER functions. If you are using an older version, you will need to rely on the traditional LEFT, MID, FIND, and VALUE functions to parse the text.

Solution 1Recommended

Extract and Calculate Decimal using TEXTBEFORE and TEXTAFTER

This is the most straightforward method for modern spreadsheet software, splitting the text perfectly at the delimiter while handling extra spaces.

This method uses the vertical bar as the exact delimiter to prevent #N/A errors. The TRIM function is crucial here as it removes any irregular spaces around the numbers before they are converted into actual numeric values by the VALUE function.

Select output cell

Click on the empty cell where you want the final decimal value to appear.

Enter the formula

Assuming your source data is in cell G3, type the following formula into the formula bar: =VALUE(TRIM(TEXTBEFORE(G3,"|")))/10^VALUE(TRIM(TEXTAFTER(G3,"|")))

Calculate the result

Press Enter to execute the formula. You can then drag the fill handle down to apply this calculation to other cells in the column.

Extract and Calculate Decimal using TEXTBEFORE and TEXTAFTER

Expected Output: For a cell containing '21730 | 8', the formula mathematically shifts the decimal point by dividing 21730 by 10 to the 8th power, correctly outputting 0.0002173.

Solution 2

Convert Text to Decimal using LEFT, MID, and FIND

Use this solution if your spreadsheet software does not support the newer TEXTBEFORE and TEXTAFTER functions.

Data Conversion in WPS

Clean and Convert Complex Data Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced text-extraction functions like TEXTBEFORE and TEXTAFTER, allowing you to seamlessly process complex text-to-decimal conversions and data cleaning tasks.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your delimited text data.
2. Apply the text-split formula: Select the destination cell and input the formula =VALUE(TRIM(TEXTBEFORE(G3,"|")))/10^VALUE(TRIM(TEXTAFTER(G3,"|"))) exactly as you would in Excel.
3. Fill the data column: Press Enter, then double-click the small square at the bottom-right corner of the cell to autofill the formula for the rest of your data.

Full compatibility with Microsoft Excel formulas and functionsBuilt-in advanced text processing and data cleaning toolsLightweight, fast, and free to use for everyday office tasksFamiliar user interface with zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does my text-splitting formula return an #N/A error?

An #N/A error usually occurs if the specified delimiter is incorrect or missing. If you try to split the text using a space (" ") but the data contains multiple spaces or uses a vertical bar ("|"), the formula fails to locate the exact split point. Using the correct delimiter alongside the TRIM function usually resolves this.

What is the purpose of the TRIM function in this formula?

The TRIM function removes any leading or trailing spaces from the text extracted by TEXTBEFORE or TEXTAFTER. Since the VALUE function requires a clean numeric string to work without errors, TRIM ensures that hidden spaces around the delimiter are safely ignored.

How do I ensure the final decimal doesn't lose leading zeros?

The formula calculates a mathematical number. If you need it to maintain a specific number of trailing or leading zeros for display purposes, right-click the cell, go to 'Format Cells', and apply a custom number format like '0.0000000'.

Excel & Data Problems