excelseparatortext-to-column

group separator in excel


I would like to add group separator to a column of texts with numbers in it, e.g. from 78898 (30.18%) to 78,898 (30.18%)

but the NUMBERVALUE function didn't work.

And if convert using the "text to columns", then (30.18%) will be converted to -30.18%.

How can I do to achieve that?

Thanks!


Solution

  • With data in A1, in B1 enter:

    =TEXT(--LEFT(A1,FIND(" ",A1)-1),"#,##0") & MID(A1,FIND(" ",A1),9999)
    

    enter image description here

    We:

    1. parse the string
    2. convert the leading part to a number
    3. format the number
    4. re-assemble the string