Convert text to rows

  • Thread starter Thread starter Radrays
  • Start date Start date
R

Radrays

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 said:
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.
Click to expand...


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
 
Hi,

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

Mike
 
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.
 
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)
 
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
 
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
 
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.
 
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)))
 
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
 
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
 
Radrays said:
Glenn I am still struggling with this one...you mentioned a possible easy
macro?
Click to expand...

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