text to columns - break by delimiter

  • Thread starter Thread starter mrl
  • Start date Start date
M

mrl

I have a list of options in one column.

Options
1,2,45
1,981
etc.

When i use text to column and break by a comma, it won't break the 1,981
because it reads that as the thousand separator.
Is there a way to reformat the original column - without loosing the comma?
or is there a way to get the text to column to recognize that column?

Thanks!
 
Hi

Replace "," with ";" using "Find/Replace" and then apply "Text to Columns"

Regards,
Per
 
When I do that it won't replace the comma in the 1,981 Is there a way to get
it to recognize that comma?
 
Use a helper column and enter this formula

=TEXT(J29,"#¤###")

then copy the helper column and go to > Edit > Paste Special > Values > Ok

Replace "¤" with ";"

Regards,
Per
 
Instead of ¤ you can put a comma and it still works:

=TEXT(J29,"#,###")

Then all your data will be comma-delimited. If you have larger numbers (with
more commas needed), you can expand the TEXT function format argument:

=TEXT(A13,"#,###,###")

Thanks for the tip, Per. I thought of using TEXT but didn't quite figure it
out.

Hutch
 
Back
Top