group separator in excel

Question

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!

Answer 1

With data in A1, in B1 enter:

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

We:

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