Splitting a cell into rows instead of columns
 

Hi, i have a cell with approx 600 names in it seperated by a semi colon. I
want to get this data into rows, so one name per cell going down the page in
list form. Text to columns does that for columns, but Excel doesnt have
enough columns for that much data (otherwise i could do text to columns then
paste special and transpose to get it in a row).

Is there a similar command to text to columns that pastes the data from a
cell into rows rather than columns?

Splitting a cell into rows instead of columns
 

Hi Tix,

Use following logic / formula, supposing you have the data in cell A1:-
Enter following formula in B1:-

=MID(A1,1,1)

Enter following formula in B2 and drag the formula till end 600 rows or more
(per situation):-

=MID($A$1,FIND(B1,$A$1)+2,1)
--
Click on Yes, if it is useful.

Thanks & Best Regards,
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)


New Delhi, India


"Tix" wrote:

Hi, i have a cell with approx 600 names in it seperated by a semi colon. I
want to get this data into rows, so one name per cell going down the page in
list form. Text to columns does that for columns, but Excel doesnt have
enough columns for that much data (otherwise i could do text to columns then
paste special and transpose to get it in a row).

Is there a similar command to text to columns that pastes the data from a
cell into rows rather than columns?

Splitting a cell into rows instead of columns
 

Hi,

You can first do text to columns using ; as a delimeter then copy the range
and then paste special - transpose. Or if you want a macro this takes the
data from a1 and splits it into column a starting in a2

Sub stance()
SrcData = Range("A1").Value
OutPutData = Split(SrcData, ";")
For SplitData = 0 To UBound(OutPutData)
Range("A" & SplitData + 1) = OutPutData(SplitData)
Next
End Sub

Mike

"Tix" wrote:

Hi, i have a cell with approx 600 names in it seperated by a semi colon. I
want to get this data into rows, so one name per cell going down the page in
list form. Text to columns does that for columns, but Excel doesnt have
enough columns for that much data (otherwise i could do text to columns then
paste special and transpose to get it in a row).

Is there a similar command to text to columns that pastes the data from a
cell into rows rather than columns?

Splitting a cell into rows instead of columns
 

Hi Dil, i tried that, i got A in B1 and then when i dragged the formula that
i copied into B2 and got an a in B2, i then dragged it down and got a blank
in B3, i in B4, g in B5, r in B6, this then repeats itself over and over.

"DILipandey" wrote:

Hi Tix,

Use following logic / formula, supposing you have the data in cell A1:-
Enter following formula in B1:-

=MID(A1,1,1)

Enter following formula in B2 and drag the formula till end 600 rows or more
(per situation):-

=MID($A$1,FIND(B1,$A$1)+2,1)
--
Click on Yes, if it is useful.

Thanks & Best Regards,
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)


New Delhi, India


"Tix" wrote:

Hi, i have a cell with approx 600 names in it seperated by a semi colon. I
want to get this data into rows, so one name per cell going down the page in
list form. Text to columns does that for columns, but Excel doesnt have
enough columns for that much data (otherwise i could do text to columns then
paste special and transpose to get it in a row).

Is there a similar command to text to columns that pastes the data from a
cell into rows rather than columns?

Splitting a cell into rows instead of columns
 

why not copy-PasteSpecial-Values, select Transpose to some other
location
apply text to columns
then come back with data in needed format
?




"Tix" wrote:
Hi, i have a cell with approx 600 names in it seperated by a semi colon.. Â*I
want to get this data into rows, so one name per cell going down the page in
list form. Â*Text to columns does that for columns, but Excel doesnt have
enough columns for that much data (otherwise i could do text to columns then
paste special and transpose to get it in a row).

Is there a similar command to text to columns that pastes the data from a
cell into rows rather than columns?- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Splitting a cell into rows instead of columns
 

Hi Mike,

I cannot do text to columns as the amount of data is greater than the amount
of columns in excel. I need to do 'text to rows' essentially but do not know
how.

"Mike H" wrote:

Hi,

You can first do text to columns using ; as a delimeter then copy the range
and then paste special - transpose. Or if you want a macro this takes the
data from a1 and splits it into column a starting in a2

Sub stance()
SrcData = Range("A1").Value
OutPutData = Split(SrcData, ";")
For SplitData = 0 To UBound(OutPutData)
Range("A" & SplitData + 1) = OutPutData(SplitData)
Next
End Sub

Mike

"Tix" wrote:

Hi, i have a cell with approx 600 names in it seperated by a semi colon. I
want to get this data into rows, so one name per cell going down the page in
list form. Text to columns does that for columns, but Excel doesnt have
enough columns for that much data (otherwise i could do text to columns then
paste special and transpose to get it in a row).

Is there a similar command to text to columns that pastes the data from a
cell into rows rather than columns?

Splitting a cell into rows instead of columns
 

Because the amount of data in the cell is bigger than the number of columns
available. If i do text to columns then i lose about half my data as the
columns run out.

I cannot transpose it as it is, as it is all in one cell.

"Jarek Kujawa" wrote:

why not copy-PasteSpecial-Values, select Transpose to some other
location
apply text to columns
then come back with data in needed format
?




"Tix" wrote:
Hi, i have a cell with approx 600 names in it seperated by a semi colon.. I
want to get this data into rows, so one name per cell going down the page in
list form. Text to columns does that for columns, but Excel doesnt have
enough columns for that much data (otherwise i could do text to columns then
paste special and transpose to get it in a row).

Is there a similar command to text to columns that pastes the data from a
cell into rows rather than columns?- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Splitting a cell into rows instead of columns
 

oopps, sorry misread yr post

presume your semicolon delimited data is in A1

then insert the following formulae:

B1: =LEFT(A1,FIND(";",A1)-1)
A2: =MID(A1,LEN(B1)+2,LEN(A1)-LEN(B1))
B2: =LEFT(A2,FIND(";",A2)-1)

then drag/copy down as needed

HIH




On 16 Kwi, 14:54, Jarek Kujawa wrote:
why not copy-PasteSpecial-Values, select Transpose to some other
location
apply text to columns
then come back with data in needed format
?





"Tix" wrote:
Hi, i have a cell with approx 600 names in it seperated by a semi colon. Â*I
want to get this data into rows, so one name per cell going down the page in
list form. Â*Text to columns does that for columns, but Excel doesnt have
enough columns for that much data (otherwise i could do text to columns then
paste special and transpose to get it in a row).

Is there a similar command to text to columns that pastes the data from a
cell into rows rather than columns?- Ukryj cytowany tekst -

- Pokaż cytowany tekst -- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Splitting a cell into rows instead of columns
 

I do not understand what any of that is but it worked so thanks very much :)

"Jarek Kujawa" wrote:

oopps, sorry misread yr post

presume your semicolon delimited data is in A1

then insert the following formulae:

B1: =LEFT(A1,FIND(";",A1)-1)
A2: =MID(A1,LEN(B1)+2,LEN(A1)-LEN(B1))
B2: =LEFT(A2,FIND(";",A2)-1)

then drag/copy down as needed

HIH




On 16 Kwi, 14:54, Jarek Kujawa wrote:
why not copy-PasteSpecial-Values, select Transpose to some other
location
apply text to columns
then come back with data in needed format
?





"Tix" wrote:
Hi, i have a cell with approx 600 names in it seperated by a semi colon. I
want to get this data into rows, so one name per cell going down the page in
list form. Text to columns does that for columns, but Excel doesnt have
enough columns for that much data (otherwise i could do text to columns then
paste special and transpose to get it in a row).

Is there a similar command to text to columns that pastes the data from a
cell into rows rather than columns?- Ukryj cytowany tekst -

- Pokaż cytowany tekst -- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Splitting a cell into rows instead of columns
 

To rows assuming cell is J2

Sub stance()
SrcData = Range("j2").Value
OutPutData = Split(SrcData, ";")
For SplitData = 0 To UBound(OutPutData)
'Range("A" & SplitData + 1) = OutPutData(SplitData)
Cells(SplitData + 3, "j") = OutPutData(SplitData)
Next
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mike H" wrote in message
...
Hi,

You can first do text to columns using ; as a delimeter then copy the
range
and then paste special - transpose. Or if you want a macro this takes the
data from a1 and splits it into column a starting in a2

Sub stance()
SrcData = Range("A1").Value
OutPutData = Split(SrcData, ";")
For SplitData = 0 To UBound(OutPutData)
Range("A" & SplitData + 1) = OutPutData(SplitData)
Next
End Sub

Mike

"Tix" wrote:

Hi, i have a cell with approx 600 names in it seperated by a semi colon.
I
want to get this data into rows, so one name per cell going down the page
in
list form. Text to columns does that for columns, but Excel doesnt have
enough columns for that much data (otherwise i could do text to columns
then
paste special and transpose to get it in a row).

Is there a similar command to text to columns that pastes the data from a
cell into rows rather than columns?

Splitting a cell into rows instead of columns
 

That might as well have been written in chinese, not being an expert in Excel
that was incomprehensible lol

Jarek gave me some formulas above however which seem to work. Just wish
Excel had a text to rows option as it does with text to columns.

"Don Guillett" wrote:

To rows assuming cell is J2

Sub stance()
SrcData = Range("j2").Value
OutPutData = Split(SrcData, ";")
For SplitData = 0 To UBound(OutPutData)
'Range("A" & SplitData + 1) = OutPutData(SplitData)
Cells(SplitData + 3, "j") = OutPutData(SplitData)
Next
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mike H" wrote in message
...
Hi,

You can first do text to columns using ; as a delimeter then copy the
range
and then paste special - transpose. Or if you want a macro this takes the
data from a1 and splits it into column a starting in a2

Sub stance()
SrcData = Range("A1").Value
OutPutData = Split(SrcData, ";")
For SplitData = 0 To UBound(OutPutData)
Range("A" & SplitData + 1) = OutPutData(SplitData)
Next
End Sub

Mike

"Tix" wrote:

Hi, i have a cell with approx 600 names in it seperated by a semi colon.
I
want to get this data into rows, so one name per cell going down the page
in
list form. Text to columns does that for columns, but Excel doesnt have
enough columns for that much data (otherwise i could do text to columns
then
paste special and transpose to get it in a row).

Is there a similar command to text to columns that pastes the data from a
cell into rows rather than columns?

Splitting a cell into rows instead of columns
 

thanks for your feedback

it is a custom in this NG to say thanks also to those whose solutions
were not used
Don gave you a neat macro...

On 16 Kwi, 16:23, Tix wrote:
That might as well have been written in chinese, not being an expert in Excel
that was incomprehensible lol

Jarek gave me some formulas above however which seem to work. Â*Just wish
Excel had a text to rows option as it does with text to columns.



"Don Guillett" wrote:
To rows assuming cell is J2

Sub stance()
SrcData = Range("j2").Value
OutPutData = Split(SrcData, ";")
For SplitData = 0 To UBound(OutPutData)
'Range("A" & SplitData + 1) = OutPutData(SplitData)
Cells(SplitData + 3, "j") = OutPutData(SplitData)
Next
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mike H" wrote in message
...
Hi,

You can first do text to columns using ; as a delimeter then copy the
range
and then paste special - transpose. Or if you want a macro this takes the
data from a1 and splits it into column a starting in a2

Sub stance()
SrcData = Range("A1").Value
OutPutData = Split(SrcData, ";")
For SplitData = 0 To UBound(OutPutData)
Range("A" & SplitData + 1) = OutPutData(SplitData)
Next
End Sub

Mike

"Tix" wrote:

Hi, i have a cell with approx 600 names in it seperated by a semi colon.
I
want to get this data into rows, so one name per cell going down the page
in
list form. Â*Text to columns does that for columns, but Excel doesnt have
enough columns for that much data (otherwise i could do text to columns
then
paste special and transpose to get it in a row).

Is there a similar command to text to columns that pastes the data from a
cell into rows rather than columns?- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Splitting a cell into rows instead of columns
 

pls note however that my formulae will give you correct result until
the last but one fragment/element
I should work a bit more to adjust them to cover la\\also the last one
element

On 16 Kwi, 15:35, Tix wrote:
I do not understand what any of that is but it worked so thanks very much :)



"Jarek Kujawa" wrote:
oopps, sorry misread yr post

presume your semicolon delimited data is in A1

then insert the following formulae:

B1: =LEFT(A1,FIND(";",A1)-1)
A2: =MID(A1,LEN(B1)+2,LEN(A1)-LEN(B1))
B2: =LEFT(A2,FIND(";",A2)-1)

then drag/copy down as needed

HIH

On 16 Kwi, 14:54, Jarek Kujawa wrote:
why not copy-PasteSpecial-Values, select Transpose to some other
location
apply text to columns
then come back with data in needed format
?

"Tix" wrote:
Hi, i have a cell with approx 600 names in it seperated by a semi colon. Â*I
want to get this data into rows, so one name per cell going down the page in
list form. Â*Text to columns does that for columns, but Excel doesnt have
enough columns for that much data (otherwise i could do text to columns then
paste special and transpose to get it in a row).

Is there a similar command to text to columns that pastes the data from a
cell into rows rather than columns?- Ukryj cytowany tekst -

- Pokaż cytowany tekst -- Ukryj cytowany tekst -

- Pokaż cytowany tekst -- Ukryj cytowany tekst -

- Pokaż cytowany tekst -