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 running MS Excel for Mac, V 16.102.1 (25101829), Office Home and Business 2021.

Years of my "moderate skills" applied with Excel but stuck on this transformation.

https://1drv.ms/x/c/4d0957bca9736d6d/Ec0gUD0-JTlCsi2fCIg8bpUBvkTobtXpP5Mt-piIA90uqg?e=AGOdCD

 

excel
Excel on Mac
Formulas and Functions

6 Replies

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Nov 11, 2025

    For Excel 365, this will strip out just about anything (even stuff CLEAN can't handle).

    =--REGEXREPLACE(B6,"[^A-Za-z0-9 .]|[\x{00A0}\x{200B}-\x{200D}]|[\t\n\r]|[\x{1F600}-\x{1F64F}]","")

     

    This was part of a diagnostic lambda I had created called Revealλ which provided a cell-for-cell description of what Excel saw in each cell (e.g. Number, number stored as text, special character, etc.).

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Nov 11, 2025

      Yes, \s covers not all white spaces. On the other hand, in this case we may use something like

      =--REGEXREPLACE(B6, "[^\d|,|.]", "")
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 11, 2025

    In addition, on Excel 365 something like

    =--REGEXREPLACE(B6,"[$|\s]","")

    , but that's not for 2021.

  • JohnVergaraD's avatar
    JohnVergaraD
    Copper Contributor
    Nov 11, 2025

    Hi, tea1982​!

    The issue is that there’s a non-breaking space character (Char 160), which often shows up when copying data from the web. You can remove this character, along with the $ symbol, like this:

    =--SUBSTITUTE(SUBSTITUTE(B6,CHAR(160),),"$",)

    Blessings!

  • 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