| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. . |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. . |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. . |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. . |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. . |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. . |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. . . |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. . . |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. . |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I convert a text array to a concatenated text cell? Excel. | Excel Worksheet Functions | |||
| Please help me with microsofts code to convert more than 65000 rows from text to excel | Excel Discussion (Misc queries) | |||
| How do you convert text to rows? | Excel Worksheet Functions | |||
| convert a range of lowercase text to upper text or vice versa | Excel Worksheet Functions | |||
| how do I easily convert a single column of text (multiple rows si. | Excel Discussion (Misc queries) | |||
Linear Mode
