Function Problems

How to Fix Excel SEARCH and LEFT Functions for Text-to-Number Conversion

Maira Mehtab Sep 28, 2026 868 views

Question details

The user needs to split text containing a slash (/) from multiple combined columns and convert the extracted text results into proper numeric values.

Product: Excel
Device & OS: not provided
Scenario: Splitting combined text strings around a delimiter (slash) and performing text-to-number conversions so the output can be used in calculations.
Observed behavior: Standard LEFT and SEARCH functions extract the desired characters, but return them as text strings rather than calculating numbers, preventing further mathematical operations.

Before you start

Before applying these formulas, ensure that the columns you are combining contain consistent data formats and that the delimiter (/) is actually present in the text string to prevent #VALUE! errors.

Solution 1Recommended

Use Double Unary Operators with TRIM, LEFT, and SEARCH

Apply the double minus sign (--) to force the text strings extracted by the LEFT or MID functions into proper numeric values, combined with TRIM to remove extra spaces.

When text functions like LEFT, MID, or RIGHT extract characters from a cell, the resulting output is formatted as text. To use these outputs in further calculations, you must convert them. Using a double unary operator (--) coerces this text back into a number. Adding the TRIM function ensures that no hidden spaces cause calculation errors.

Combine columns and extract the left portion

To get the numerical value before the slash, select cell S2 and enter the formula `=--TRIM(LEFT(P2&Q2&R2,SEARCH("/",P2&Q2&R2)-1))`.

Extract the right portion using MID

To extract the numerical value after the slash, select cell T2 and enter `=--TRIM(MID(P2&Q2&R2,SEARCH("/",P2&Q2&R2)+1,9))`. Note: Change the '9' to a higher number if the extracted text string might be longer than 9 characters.

Apply formulas to the rest of the column

Highlight both cells S2 and T2. Click and drag the fill handle (the small square at the bottom-right corner of the selection) down your worksheet to apply these formulas to the remaining rows.

Understanding the Double Unary (--): The double unary operator acts as a shortcut to efficiently convert text digits into actual numerical values, serving as a faster alternative to using the VALUE() function.

Efficient Spreadsheet Management

Easily Split and Convert Text to Numbers in WPS Spreadsheet

WPS Spreadsheet fully supports advanced text functions like LEFT, SEARCH, MID, and TRIM. It allows you to seamlessly extract text and convert it to numbers using identical formulas to Microsoft Excel.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open the workbook containing the text strings you want to extract and convert.
2. Enter the text-to-number formula: Select the target cell and input your double unary formula, such as `=--TRIM(LEFT(P2&Q2&R2,SEARCH("/",P2&Q2&R2)-1))`.
3. Fill down the column: Click and drag the fill handle at the bottom right corner of the cell to copy the formula down your dataset.

100% compatibility with Microsoft Excel formulas and functions.Advanced data extraction and text-to-number conversion capabilities.Lightweight software with a familiar, easy-to-use interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel return a #VALUE! error when using the SEARCH function?

The #VALUE! error occurs if the SEARCH function cannot find the specified character (like a slash) within the referenced text string. Ensure the delimiter exists, or use the IFERROR function to handle cells that lack the delimiter gracefully.

What is the purpose of the double minus sign (--) in Excel formulas?

The double minus sign, known as a double unary operator, coerces text representations of numbers (which are often the result of text manipulation functions like LEFT or MID) into actual numeric values so they can be processed mathematically.

Can I use the VALUE function instead of the double unary operator?

Yes, wrapping your text extraction formula in the VALUE function (e.g., `=VALUE(TRIM(LEFT(...)))`) achieves the exact same result as using the double unary operator (--), safely converting the extracted text into a calculable number.

How does the TRIM function help when extracting text?

When splitting combined columns, extra spaces might inadvertently be included in your extraction. The TRIM function removes all leading and trailing spaces, preventing conversion errors when coercing the extracted string into a numeric value.

Excel & Data Problems