text to columns - break by delimiter
 

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!

text to columns - break by delimiter
 

Hi

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

Regards,
Per

"mrl" skrev i meddelelsen
...
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!

text to columns - break by delimiter
 

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?

"Per Jessen" wrote:

Hi

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

Regards,
Per

"mrl" skrev i meddelelsen
...
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!

text to columns - break by delimiter
 

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

"mrl" skrev i meddelelsen
...
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?

"Per Jessen" wrote:

Hi

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

Regards,
Per

"mrl" skrev i meddelelsen
...
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!

text to columns - break by delimiter
 

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

"Per Jessen" wrote:

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

"mrl" skrev i meddelelsen
...
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?

"Per Jessen" wrote:

Hi

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

Regards,
Per

"mrl" skrev i meddelelsen
...
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!