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 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.

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.
.

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.
.

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.
.

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.
.

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.
.

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.
.

.

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.

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.

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.

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.

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.

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.
.

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.