Forum Discussion

tea1982's avatar
tea1982
Copper Contributor
Nov 09, 2025

Another issue with Value transformation.

I've played for a couple of days with this, having tried a number of different solutions that worked for others. Cannot figure out how to make a text field convert to value. Here is my XLS. I am runn...
excel
Excel on Mac
Formulas and Functions
Lorenzo's avatar
Lorenzo
Silver Contributor
Nov 11, 2025

Hi

As shown in D6 your amt contains an additional invisible/unprintable char. (probably due to a copy/paste from the web). To convert it to a number, one way:

=NUMBERVALUE( LEFT( B6, LEN( B6 ) -1 ) )

(tried to get rid of the invisible/unprintable char. with CLEAN(B6) - didn't work)

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Nov 11, 2025

    I also tried CLEAN and TRIM but neither worked.  The character is a non-breaking space which apparently isn't handled by TRIM or CLEAN.  That said some alternatives include:

    =--SUBSTITUTE(B6,CHAR(160),)
    =VALUE(TEXTBEFORE(B6,CHAR(160)))

    but basically you need something to remove the non-breaking space and then something to convert that result to a number.  So I used SUBSTITUTE and TEXTBEFORE as ways to remove that non-breaking space, and then I used VALUE or just -- to convert the result to a number but there are many other options for each of those steps.

Resources