ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Convert text to rows (https://www.excelbanter.com/excel-worksheet-functions/246852-convert-text-rows.html)

Radrays
Convert text to rows
 
I am currently using 2007 - I have about 20,000 rows by 12 columns of
information in a spreadsheet.

Column "G" cells will at times have more than 1 number in it.
How can I split the cell so each number within that cell is in it's own row?

Thank you.

Glenn
Convert text to rows
 
Radrays wrote:
I am currently using 2007 - I have about 20,000 rows by 12 columns of
information in a spreadsheet.

Column "G" cells will at times have more than 1 number in it.
How can I split the cell so each number within that cell is in it's own row?

Thank you.


Give an example of the data in column G.

Mike H
Convert text to rows
 
Hi,

Not enough information.
Is (for example) 21 one number or 2
are the numbers delimited in any way

Mike


"Radrays" wrote:

I am currently using 2007 - I have about 20,000 rows by 12 columns of
information in a spreadsheet.

Column "G" cells will at times have more than 1 number in it.
How can I split the cell so each number within that cell is in it's own row?

Thank you.

Mike H
Convert text to rows
 
Hi,

Not enough information.
Is (for example) 21 one number or 2
are the numbers delimited in any way

Mike


"Radrays" wrote:

I am currently using 2007 - I have about 20,000 rows by 12 columns of
information in a spreadsheet.

Column "G" cells will at times have more than 1 number in it.
How can I split the cell so each number within that cell is in it's own row?

Thank you.

Radrays
Convert text to rows
 
The numbers are in a sequence such as "89051 001 89060 001" whereas each
number 8 begins a new number. Some cells will have just 1 set of numbers
(89051 001) others may have as many as 6 sets of numbers.

"Mike H" wrote:

Hi,

Not enough information.
Is (for example) 21 one number or 2
are the numbers delimited in any way

Mike


"Radrays" wrote:

I am currently using 2007 - I have about 20,000 rows by 12 columns of
information in a spreadsheet.

Column "G" cells will at times have more than 1 number in it.
How can I split the cell so each number within that cell is in it's own row?

Thank you.

Radrays
Convert text to rows
 
88230 001 88262 001 88289 001 88291 001
whereas each number 88 begins a new sequence.

"Glenn" wrote:

Radrays wrote:
I am currently using 2007 - I have about 20,000 rows by 12 columns of
information in a spreadsheet.

Column "G" cells will at times have more than 1 number in it.
How can I split the cell so each number within that cell is in it's own row?

Thank you.


Give an example of the data in column G.
.

Glenn
Convert text to rows
 
Probably an easy macro solution to this, but here is a way using worksheet
formulas. Assuming your data is in A2:L20000, and that the rest of the sheet is
empty, and that the "numbers" in column G are always in the format of "five
digits <space three digits", enter the number 1 in M2 and then the following
array formula (commit with CTRL+SHIFT+ENTER) in M3 and copy down:

=SUM((LEN(TRIM($G$2:G2))-LEN(SUBSTITUTE(TRIM($G$2:G2)," ",""))-1)/2+1)+1

Then add the following:

N2 =MATCH(ROW(A1),M:M,1)-1
O2 =INDEX($A$2:$L$6,$N2,COLUMN(A1))

Copy N2 down and O2 down and across to column Z.

Change U2 as follows and copy down:

=MID(SUBSTITUTE(" "&INDEX($A$2:$L$6,$N2,
COLUMN(G1))&" "," ","@",COUNTIF($N$2:N2,N2)*2-1),
FIND("@",SUBSTITUTE(" "&INDEX($A$2:$L$6,$N2,
COLUMN(G1))&" "," ","@",COUNTIF($N$2:N2,N2)*2-1))+1,9)


Radrays wrote:
The numbers are in a sequence such as "89051 001 89060 001" whereas each
number 8 begins a new number. Some cells will have just 1 set of numbers
(89051 001) others may have as many as 6 sets of numbers.

"Mike H" wrote:

Hi,

Not enough information.
Is (for example) 21 one number or 2
are the numbers delimited in any way

Mike


"Radrays" wrote:

I am currently using 2007 - I have about 20,000 rows by 12 columns of
information in a spreadsheet.

Column "G" cells will at times have more than 1 number in it.
How can I split the cell so each number within that cell is in it's own row?

Thank you.

Jacob Skaria
Convert text to rows
 
Copy the below formula to a cell and copy/drag across to the right as
required...

=IF(COLUMN(A1)(LEN($G1)+1-LEN(SUBSTITUTE(" "&$G1,"
88",)))/3,"","88")&TRIM(MID(SUBSTITUTE(" "&$G1&REPT(" 88",6),"
88",REPT(CHAR(32),255)),COLUMNS($B$1:B$1)*255,255) )

If this post helps click Yes
---------------
Jacob Skaria


"Radrays" wrote:

88230 001 88262 001 88289 001 88291 001
whereas each number 88 begins a new sequence.

"Glenn" wrote:

Radrays wrote:
I am currently using 2007 - I have about 20,000 rows by 12 columns of
information in a spreadsheet.

Column "G" cells will at times have more than 1 number in it.
How can I split the cell so each number within that cell is in it's own row?

Thank you.


Give an example of the data in column G.
.

Jacob Skaria
Convert text to rows
 
Much more better one

=IF(COLUMN(A1)(LEN($G1)+1-LEN(SUBSTITUTE(" "& $G1,
" "&LEFT($G1,2),)))/3,"",LEFT($G1,2))&TRIM(MID(
SUBSTITUTE(" "&$G1&REPT(" "&LEFT($G1,2),6)," "&
LEFT($G1,2),REPT(CHAR(32),255)),COLUMNS($B$1:B$1)* 255,255))

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Copy the below formula to a cell and copy/drag across to the right as
required...

=IF(COLUMN(A1)(LEN($G1)+1-LEN(SUBSTITUTE(" "&$G1,"
88",)))/3,"","88")&TRIM(MID(SUBSTITUTE(" "&$G1&REPT(" 88",6),"
88",REPT(CHAR(32),255)),COLUMNS($B$1:B$1)*255,255) )

If this post helps click Yes
---------------
Jacob Skaria


"Radrays" wrote:

88230 001 88262 001 88289 001 88291 001
whereas each number 88 begins a new sequence.

"Glenn" wrote:

Radrays wrote:
I am currently using 2007 - I have about 20,000 rows by 12 columns of
information in a spreadsheet.

Column "G" cells will at times have more than 1 number in it.
How can I split the cell so each number within that cell is in it's own row?

Thank you.


Give an example of the data in column G.
.

Glenn
Convert text to rows
 
I don't think this answers his question:

"How can I split the cell so each number within that cell is in it's own *row*?"

Unless he meant what he didn't say.


Jacob Skaria wrote:
Copy the below formula to a cell and copy/drag across to the right as
required...

=IF(COLUMN(A1)(LEN($G1)+1-LEN(SUBSTITUTE(" "&$G1,"
88",)))/3,"","88")&TRIM(MID(SUBSTITUTE(" "&$G1&REPT(" 88",6),"
88",REPT(CHAR(32),255)),COLUMNS($B$1:B$1)*255,255) )

If this post helps click Yes
---------------
Jacob Skaria


"Radrays" wrote:

88230 001 88262 001 88289 001 88291 001
whereas each number 88 begins a new sequence.

"Glenn" wrote:

Radrays wrote:
I am currently using 2007 - I have about 20,000 rows by 12 columns of
information in a spreadsheet.

Column "G" cells will at times have more than 1 number in it.
How can I split the cell so each number within that cell is in it's own row?

Thank you.

Give an example of the data in column G.
.

Glenn
Convert text to rows
 
If you don't want the data from columns A:F and H:L to repeat, change O2 to this
before copying across and down:

=IF($N2=$N1,"",INDEX($A$2:$L$6,$N2,COLUMN(A1)))


Glenn wrote:
Probably an easy macro solution to this, but here is a way using
worksheet formulas. Assuming your data is in A2:L20000, and that the
rest of the sheet is empty, and that the "numbers" in column G are
always in the format of "five digits <space three digits", enter the
number 1 in M2 and then the following array formula (commit with
CTRL+SHIFT+ENTER) in M3 and copy down:

=SUM((LEN(TRIM($G$2:G2))-LEN(SUBSTITUTE(TRIM($G$2:G2)," ",""))-1)/2+1)+1

Then add the following:

N2 =MATCH(ROW(A1),M:M,1)-1
O2 =INDEX($A$2:$L$6,$N2,COLUMN(A1))

Copy N2 down and O2 down and across to column Z.

Change U2 as follows and copy down:

=MID(SUBSTITUTE(" "&INDEX($A$2:$L$6,$N2,
COLUMN(G1))&" "," ","@",COUNTIF($N$2:N2,N2)*2-1),
FIND("@",SUBSTITUTE(" "&INDEX($A$2:$L$6,$N2,
COLUMN(G1))&" "," ","@",COUNTIF($N$2:N2,N2)*2-1))+1,9)


Radrays wrote:
The numbers are in a sequence such as "89051 001 89060 001" whereas
each number 8 begins a new number. Some cells will have just 1 set of
numbers (89051 001) others may have as many as 6 sets of numbers.

"Mike H" wrote:

Hi,

Not enough information.
Is (for example) 21 one number or 2
are the numbers delimited in any way

Mike


"Radrays" wrote:

I am currently using 2007 - I have about 20,000 rows by 12 columns
of information in a spreadsheet.

Column "G" cells will at times have more than 1 number in it.
How can I split the cell so each number within that cell is in it's
own row?

Thank you.

Jacob Skaria
Convert text to rows
 
Reading your question subject "text to rows" I am a bit confused...Do you
mean to separate rows?

I have worked under the assumption that your ColG has got this numbers and
you are looking at splitting that to columns ..Since there are 12 columns of
data the formula is to be applied to M1 and copied to N1,O1 etc;

ColG
88230 001 88262 001 88289 001 88291 001
89051 001 89060 001
--
--

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Much more better one

=IF(COLUMN(A1)(LEN($G1)+1-LEN(SUBSTITUTE(" "& $G1,
" "&LEFT($G1,2),)))/3,"",LEFT($G1,2))&TRIM(MID(
SUBSTITUTE(" "&$G1&REPT(" "&LEFT($G1,2),6)," "&
LEFT($G1,2),REPT(CHAR(32),255)),COLUMNS($B$1:B$1)* 255,255))

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Copy the below formula to a cell and copy/drag across to the right as
required...

=IF(COLUMN(A1)(LEN($G1)+1-LEN(SUBSTITUTE(" "&$G1,"
88",)))/3,"","88")&TRIM(MID(SUBSTITUTE(" "&$G1&REPT(" 88",6),"
88",REPT(CHAR(32),255)),COLUMNS($B$1:B$1)*255,255) )

If this post helps click Yes
---------------
Jacob Skaria


"Radrays" wrote:

88230 001 88262 001 88289 001 88291 001
whereas each number 88 begins a new sequence.

"Glenn" wrote:

Radrays wrote:
I am currently using 2007 - I have about 20,000 rows by 12 columns of
information in a spreadsheet.

Column "G" cells will at times have more than 1 number in it.
How can I split the cell so each number within that cell is in it's own row?

Thank you.


Give an example of the data in column G.
.

Radrays
Convert text to rows
 
I need to split the cell into seperate rows so each number will then appear
on it own row.

"Jacob Skaria" wrote:

Reading your question subject "text to rows" I am a bit confused...Do you
mean to separate rows?

I have worked under the assumption that your ColG has got this numbers and
you are looking at splitting that to columns ..Since there are 12 columns of
data the formula is to be applied to M1 and copied to N1,O1 etc;

ColG
88230 001 88262 001 88289 001 88291 001
89051 001 89060 001
--
--

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Much more better one

=IF(COLUMN(A1)(LEN($G1)+1-LEN(SUBSTITUTE(" "& $G1,
" "&LEFT($G1,2),)))/3,"",LEFT($G1,2))&TRIM(MID(
SUBSTITUTE(" "&$G1&REPT(" "&LEFT($G1,2),6)," "&
LEFT($G1,2),REPT(CHAR(32),255)),COLUMNS($B$1:B$1)* 255,255))

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Copy the below formula to a cell and copy/drag across to the right as
required...

=IF(COLUMN(A1)(LEN($G1)+1-LEN(SUBSTITUTE(" "&$G1,"
88",)))/3,"","88")&TRIM(MID(SUBSTITUTE(" "&$G1&REPT(" 88",6),"
88",REPT(CHAR(32),255)),COLUMNS($B$1:B$1)*255,255) )

If this post helps click Yes
---------------
Jacob Skaria


"Radrays" wrote:

88230 001 88262 001 88289 001 88291 001
whereas each number 88 begins a new sequence.

"Glenn" wrote:

Radrays wrote:
I am currently using 2007 - I have about 20,000 rows by 12 columns of
information in a spreadsheet.

Column "G" cells will at times have more than 1 number in it.
How can I split the cell so each number within that cell is in it's own row?

Thank you.


Give an example of the data in column G.
.

Jacob Skaria
Convert text to rows
 
You can try out the below macro which will extract all these numbers to ColM.
If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()


Sub Macro()
Dim lngRow As Long, lngLastRow As Long, lngDestRow As Long
lngLastRow = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row
For lngRow = 1 To lngLastRow
If Trim(Range("G" & lngRow)) < "" Then
arrdata = Split(" " & Range("G" & lngRow), " " & _
Left(Range("G" & lngRow), 2))
For intTemp = 1 To UBound(arrdata)
lngDestRow = lngDestRow + 1
Range("M" & lngDestRow) = Left(Range("G" & lngRow), 2) & _
arrdata(intTemp)
Next
End If
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Radrays" wrote:

I need to split the cell into seperate rows so each number will then appear
on it own row.

"Jacob Skaria" wrote:

Reading your question subject "text to rows" I am a bit confused...Do you
mean to separate rows?

I have worked under the assumption that your ColG has got this numbers and
you are looking at splitting that to columns ..Since there are 12 columns of
data the formula is to be applied to M1 and copied to N1,O1 etc;

ColG
88230 001 88262 001 88289 001 88291 001
89051 001 89060 001
--
--

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Much more better one

=IF(COLUMN(A1)(LEN($G1)+1-LEN(SUBSTITUTE(" "& $G1,
" "&LEFT($G1,2),)))/3,"",LEFT($G1,2))&TRIM(MID(
SUBSTITUTE(" "&$G1&REPT(" "&LEFT($G1,2),6)," "&
LEFT($G1,2),REPT(CHAR(32),255)),COLUMNS($B$1:B$1)* 255,255))

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Copy the below formula to a cell and copy/drag across to the right as
required...

=IF(COLUMN(A1)(LEN($G1)+1-LEN(SUBSTITUTE(" "&$G1,"
88",)))/3,"","88")&TRIM(MID(SUBSTITUTE(" "&$G1&REPT(" 88",6),"
88",REPT(CHAR(32),255)),COLUMNS($B$1:B$1)*255,255) )

If this post helps click Yes
---------------
Jacob Skaria


"Radrays" wrote:

88230 001 88262 001 88289 001 88291 001
whereas each number 88 begins a new sequence.

"Glenn" wrote:

Radrays wrote:
I am currently using 2007 - I have about 20,000 rows by 12 columns of
information in a spreadsheet.

Column "G" cells will at times have more than 1 number in it.
How can I split the cell so each number within that cell is in it's own row?

Thank you.


Give an example of the data in column G.
.

Radrays
Convert text to rows
 
Exactly - "How can I split the cell so each number within that cell is in
it's own *row*?"


"Glenn" wrote:

I don't think this answers his question:

"How can I split the cell so each number within that cell is in it's own *row*?"

Unless he meant what he didn't say.


Jacob Skaria wrote:
Copy the below formula to a cell and copy/drag across to the right as
required...

=IF(COLUMN(A1)(LEN($G1)+1-LEN(SUBSTITUTE(" "&$G1,"
88",)))/3,"","88")&TRIM(MID(SUBSTITUTE(" "&$G1&REPT(" 88",6),"
88",REPT(CHAR(32),255)),COLUMNS($B$1:B$1)*255,255) )

If this post helps click Yes
---------------
Jacob Skaria


"Radrays" wrote:

88230 001 88262 001 88289 001 88291 001
whereas each number 88 begins a new sequence.

"Glenn" wrote:

Radrays wrote:
I am currently using 2007 - I have about 20,000 rows by 12 columns of
information in a spreadsheet.

Column "G" cells will at times have more than 1 number in it.
How can I split the cell so each number within that cell is in it's own row?

Thank you.

Give an example of the data in column G.
.

.

Glenn
Convert text to rows
 
Did you look at my other responses in this thread?

Radrays wrote:
Exactly - "How can I split the cell so each number within that cell is in
it's own *row*?"


"Glenn" wrote:

I don't think this answers his question:

"How can I split the cell so each number within that cell is in it's own *row*?"

Unless he meant what he didn't say.


Jacob Skaria wrote:
Copy the below formula to a cell and copy/drag across to the right as
required...

=IF(COLUMN(A1)(LEN($G1)+1-LEN(SUBSTITUTE(" "&$G1,"
88",)))/3,"","88")&TRIM(MID(SUBSTITUTE(" "&$G1&REPT(" 88",6),"
88",REPT(CHAR(32),255)),COLUMNS($B$1:B$1)*255,255) )

If this post helps click Yes
---------------
Jacob Skaria


"Radrays" wrote:

88230 001 88262 001 88289 001 88291 001
whereas each number 88 begins a new sequence.

"Glenn" wrote:

Radrays wrote:
I am currently using 2007 - I have about 20,000 rows by 12 columns of
information in a spreadsheet.

Column "G" cells will at times have more than 1 number in it.
How can I split the cell so each number within that cell is in it's own row?

Thank you.
Give an example of the data in column G.
.

.

Radrays
Convert text to rows
 
Glenn I am still struggling with this one...you mentioned a possible easy
macro?

"Glenn" wrote:

Probably an easy macro solution to this, but here is a way using worksheet
formulas. Assuming your data is in A2:L20000, and that the rest of the sheet is
empty, and that the "numbers" in column G are always in the format of "five
digits <space three digits", enter the number 1 in M2 and then the following
array formula (commit with CTRL+SHIFT+ENTER) in M3 and copy down:

=SUM((LEN(TRIM($G$2:G2))-LEN(SUBSTITUTE(TRIM($G$2:G2)," ",""))-1)/2+1)+1

Then add the following:

N2 =MATCH(ROW(A1),M:M,1)-1
O2 =INDEX($A$2:$L$6,$N2,COLUMN(A1))

Copy N2 down and O2 down and across to column Z.

Change U2 as follows and copy down:

=MID(SUBSTITUTE(" "&INDEX($A$2:$L$6,$N2,
COLUMN(G1))&" "," ","@",COUNTIF($N$2:N2,N2)*2-1),
FIND("@",SUBSTITUTE(" "&INDEX($A$2:$L$6,$N2,
COLUMN(G1))&" "," ","@",COUNTIF($N$2:N2,N2)*2-1))+1,9)


Radrays wrote:
The numbers are in a sequence such as "89051 001 89060 001" whereas each
number 8 begins a new number. Some cells will have just 1 set of numbers
(89051 001) others may have as many as 6 sets of numbers.

"Mike H" wrote:

Hi,

Not enough information.
Is (for example) 21 one number or 2
are the numbers delimited in any way

Mike


"Radrays" wrote:

I am currently using 2007 - I have about 20,000 rows by 12 columns of
information in a spreadsheet.

Column "G" cells will at times have more than 1 number in it.
How can I split the cell so each number within that cell is in it's own row?

Thank you.
.

Glenn
Convert text to rows
 
Radrays wrote:
Glenn I am still struggling with this one...you mentioned a possible easy
macro?



Yes, but I'm not a macro/VBA kind of guy. I assumed someone else would propose
something, and Jacob did. Not sure if it works for you. If not, maybe post a
small example (maybe half a dozen lines and columns) of your original data and
how you want it to look after it is processed.


All times are GMT +1. The time now is 04:59 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com