text to columns (vertically stacked)

[JamesGale]

Hi,

Had a quick browse through the forums for an answer but as it is quite hard to describe i cant quite find the answer.

Basically I need to split some cells but they have stacked text in them i.e

Cell a1 shows:

666666
part 77777 x 20
5x s452563

Cell b1 shows:

1x 254684564
3x 4481211111 & 5 ea g8373

etc.

When i run the text to columns function i only get the first line of the data, i could ideally like to split the data by spaces and/ or line breaks.

Any ideas?

[shg]

Run text to columns, and in the Other box, press and hold the Alt key, and on the NUMERIC keypad, type 0010 (which is a line feed).

[JamesGale]

I am failing to get this to work.

when i hold 'alt' and type '0010' nothing comes out and i get no result.

what am i doing wrong?

[JamesGale]

bump?...bump?

[shg]

There's not much more to explain, James. Alt key, NUMERIC keypad. You won't see anything on the Other box, but you will a change in the Data preview window, assuming there are line feeds in your data.

[JamesGale]

well i understand that but cannot get it to work. should this work in excel 2003?

I have attached a sample cell, could you take a look please and just confirm that it can be done this way?

[shg]

Your data also has carriage returns. In an adjacent column,

=SUBSTITUTE(A1, CHAR(13), "")

Then copy that column, replace with values, and proceed as previously described. We'd have gotten there in one step if you posted an example to begin with ...

[JamesGale]

thank you very much. got it sorted now.

[Donapg]

Hi James, I do not live in the great state of Texas and I am not a forum guru but the easiest way I found to transpose text in excel 2003 and 2007 is .. the function transpose. See here:

excel 2003 : http://www.techonthenet.com/excel/fo.../transpose.php
excel 2007: http://office.microsoft.com/en-us/ex...010224502.aspx

In excel 2007 there is - under paste special - the related funcion.
Hope this helps further,
Best regards,
Donberti from good old Europe

[rajuj]

To
shg forum guru

It is an excellent solution to spit cells with your alt and numeric key pad solution. can iknow, the similar keypad strokes for some of the keystrokes, which does not type but you get the results, like carriage returns and some more key strokes. It will be very useful.

I know that I am continuing a old thread, but to acknowledge a best solution, I acknowledges and continue my query. What are the key strokes, that we can use in text to column in other, which permit only one key stroke, a small box in the delimiter.