tetx to columns not working

[qwertyjjj]

I am trying to convert some text with comma separated values into columns but this is not working:

330,335 336,337 343 351,353 354,355,357,358 369,370,373 375,380,381,382,383,384 385,386,387,388,389,390,391,392 393,394,395,397,398,399

As you can see some columns have 1 comma, some multiple and I need each laid out into a single cel so I can do some lookups.
Any ideas why this isn;t working?
The space values in the above data is actually a new column in the sheet.

[Fotis1991]

Something like this, using text to columns?

[:) Sixthsense :)]

Just a quick check, place the cursor in any of that cell which is having the values with comma Press F2 whether the comma is is displaying in formula bar? If the comma is not showing in formula bar means there is no comma and that is the reason the Text To Columns failed to split.

Hope that helps!

[qwertyjjj]

Just a quick check, place the cursor in any of that cell which is having the values with comma Press F2 whether the comma is is displaying in formula bar? If the comma is not showing in formula bar means there is no comma and that is the reason the Text To Columns failed to split.

Hope that helps!

sometimes the comma is in the forumla bar, sometimes not.

[:) Sixthsense :)]

The reason is if the comma is representing with the text data then it will appear in formula bar. It the comma is lying with the numbers then excel remove the comma and treat it as continuous number.

[qwertyjjj]

The reason is if the comma is representing with the text data then it will appear in formula bar. It the comma is lying with the numbers then excel remove the comma and treat it as continuous number.

So, I have to convert all the cells to text? Or do I have to add a ' in front of all the numbers?

[:) Sixthsense :)]

Lot of methods are available I hope you should have exported it from any software or from some other source. Before pasting the data into excel just select the column or range and change the formatting as text. Use paste special and paste the data in excel as values and after that we can apply formula and extract each data by considering the comma as a separator.

[qwertyjjj]

Lot of methods are available I hope you should have exported it from any software or from some other source. Before pasting the data into excel just select the column or range and change the formatting as text. Use paste special and paste the data in excel as values and after that we can apply formula and extract each data by considering the comma as a separator.

No, I can't get that to work either.
Whether I add commas or paste as values into text fields it is still changing 330,335 to 330355 instead of 330 | 355

[:) Sixthsense :)]

No, I can't get that to work either.
Whether I add commas or paste as values into text fields it is still changing 330,335 to 330355 instead of 330 | 355

I don't want you to spend more time in converting the numbers to text. Follow the below method

Copy the data and open New Microsoft Word and press Alt+E+S+U and paste the data

Press Ctrl+Shift+8

Now press Ctrl+H to get the find and replace dialog box and

in Find What type comma (,)
in Replace With type ^t

Click Replace All. Now copy and paste the converted data to excel.

In word again press Ctrl+Shift+8 to remove the paragraph marks.

^t will apply Tab. Tabbed Data will get placed in individual excel cell.

Using ^p will in Replace With will move the data to next row.

Hope that helps!

[qwertyjjj]

Copy the data and open New Microsoft Word and press Alt+E+S+U and paste the data

Press Ctrl+Shift+8

Now press Ctrl+H to get the find and replace dialog box and

in Find What type comma (,)
in Replace With type ^t

Click Replace All. Now copy and paste the converted data to excel.

stuck here, I pasted into excel and get 335 345 | 456 457 458 | 376 |
but then you say to go back to word?
In word again press Ctrl+Shift+8 to remove the paragraph marks.

^t will apply Tab. Tabbed Data will get placed in individual excel cell.

Using ^p will in Replace With will move the data to next row.

Also, I cannot replace ^t in excel, it doesn;t put them in new cells.

[:) Sixthsense :)]

I just given some additional tips once you pasted in excel means the task is over. But the values should move to cell to cell but in your description its staying in same cell. If you dont have any issues then just send the Microsoft word data (with comma) I will split it and paste and send it in excel.