Extract predefined numbers of characters, and join them in one cell

[Kishan]

Using Excel 2000</SPAN></SPAN>

Hi,</SPAN></SPAN>

1-My data columns C:P in cells</SPAN></SPAN>
2-Predefined numbers columns R:Z</SPAN></SPAN>
3-Extract and join them in columns AB:AJ</SPAN></SPAN>

Extract predefined number of characters from columns C:P (as per specified in columns R:Z) and join them in columns AB:AJ</SPAN></SPAN>

Example for row6 R6:Z6 predefine number is 1, 2, 2, 2, 3, 1, 3, 0 & 0.</SPAN></SPAN>
So far extract 1 character from C6 = "X" join in cell AB6</SPAN></SPAN>
So far extract 2 character from D6, E6 = "12" join in cell AC6</SPAN></SPAN>
So far extract 2 character from F6, G6 = "12" join in cell AD6</SPAN></SPAN>
So far extract 2 characters from H6, I6 = "XX" join in cell AE6</SPAN></SPAN>
So far extract 3 character from J6, K6, L6 = "112" join in cell AF6</SPAN></SPAN>
So far extract 1 character from M6 = "X" join in cell AG6</SPAN></SPAN>
So far extract 3 character from N6, O6, P6 = "112" join in cell AH6</SPAN></SPAN>

And the same method will be followed for rest of the data rows</SPAN></SPAN>

Example data...</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1
2
3
4
5			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14		B1	B2	B3	B4	B5	B6	B7	B8	B9		B1	B2	B3	B4	B5	B6	B7	B8	B9
6			X	1	2	1	2	X	X	1	1	2	X	1	1	2		1	2	2	2	3	1	3	0	0		X	12	12	XX	112	X	112
7			1	X	2	2	2	1	X	X	2	2	X	X	1	1		5	5	2	2	0	0	0	0	0		1X222	1XX22	XX	11
8			1	X	1	1	X	X	2	2	X	X	1	1	X	X		2	6	2	4	0	0	0	0	0		1X	11XX22	XX	11XX
9			1	X	1	2	2	1	X	1	1	X	1	2	2	1		2	3	2	3	3	1	0	0	0		1X	122	1X	11XX	122	1
10			X	1	X	1	X	1	1	X	1	1	1	X	X	1		1	2	2	3	5	1	0	0	0		X	1X	1X	11X	111XX	1
11			X	X	X	1	1	1	1	X	1	X	2	1	2	X		3	5	3	2	1	0	0	0	0		XXX	1111X	1X2	12	X
12			2	X	1	1	1	2	X	X	X	1	1	1	2	1		1	1	4	3	4	1	0	0	0		2	X	1112	XXX	1112	1
13			X	1	X	X	1	1	1	1	1	1	1	X	1	1		1	3	8	2	0	0	0	0	0		X	1XX	1111111X	11
14			1	X	X	1	2	1	2	1	1	1	2	X	2	X		3	2	2	4	2	1	0	0	0		1XX	12	12	1112	X2	X
15			X	2	X	1	1	X	1	1	1	1	X	1	1	X		2	1	3	5	3	0	0	0	0		X2	X	11X	1111X	11X
16			1	1	1	1	1	1	X	X	X	1	1	1	2	X		9	4	1	0	0	0	0	0	0		111111XXX	1112	X
17			1	2	1	2	X	1	1	2	X	2	1	1	1	X		2	2	1	3	2	4	0	0	0		12	12	X	112	X2	111X
18			1	X	X	1	1	2	X	X	1	X	1	1	X	1		3	3	2	2	3	1	0	0	0		1XX	112	XX	1X	11X	1
19			1	1	1	1	1	X	X	2	1	1	1	1	1	2		8	6	0	0	0	0	0	0	0		11111XX2	111112
20			1	1	1	1	1	1	1	1	1	1	2	X	X	1		11	2	1	0	0	0	0	0	0		11111111112	XX	1
21
22
23
24
Sheet8

Thank you in advance</SPAN></SPAN>

Regards,</SPAN>
Kishan</SPAN></SPAN>

 

[Rick Rothstein]

I know you are using Excel 2000 which I do not have, so I cannot test this, but does the following macro work for you...

[table="width: 500"]
[tr]
	[td]Sub ExtractAndJoin()
  Dim R As Long, C As Long, LastRow As Long
  Dim Chars As String, Pattern As String
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  For R = 6 To LastRow
    Chars = ""
    Pattern = ""
    For C = 3 To 16
      Chars = Chars & Cells(R, C).Value
    Next
    For C = 18 To 26
      Pattern = Pattern & " " & String(Cells(R, C).Value, "@")
    Next
    Cells(R, "AA").Resize(, 9) = Split(Format(Chars, "!" & Pattern))
  Next
End Sub[/td]
[/tr]
[/table]

Just so I know for future questions, if you execute the following line of code in the VB editor's Immediate Window with the data sheet you posted active, what happens...

[table="width: 500"]
[tr]
	[td]? Join(WorksheetFunction.Index(Range("C6:P6").Value, 1, 0), "")[/td]
[/tr]
[/table]

 

[Kishan]

I know you are using Excel 2000 which I do not have, so I cannot test this, but does the following macro work for you...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub ExtractAndJoin()
  Dim R As Long, C As Long, LastRow As Long
  Dim Chars As String, Pattern As String
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  For R = 6 To LastRow
    Chars = ""
    Pattern = ""
    For C = 3 To 16
      Chars = Chars & Cells(R, C).Value
    Next
    For C = 18 To 26
      Pattern = Pattern & " " & String(Cells(R, C).Value, "@")
    Next
    Cells(R, "AA").Resize(, 9) = Split(Format(Chars, "!" & Pattern))
  Next
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Excellent!! Rick, code is running like magic, tried with my original data 9000+ rows , and take a few second to join result data in columns AB:AK

Thank you very much for giving a quick and spot on solution

I do appreciate your kind help for solving my issue

Have a good weekend

Kind regards,
Kishan

[/CODE]
Just so I know for future questions, if you execute the following line of code in the VB editor's Immediate Window with the data sheet you posted active, what happens...

[TABLE="width: 500"]
<tbody>[TR]
[TD]? Join(WorksheetFunction.Index(Range("C6:P6").Value, 1, 0), "")[/TD]
[/TR]
</tbody>[/TABLE]

Regarding the line above if I understood correctly, I placed the line activating posted sheet in the VB editor's Immediate Window I see does not happen any action. Did I do it correctly not sure?

 

[Rick Rothstein]

Regarding the line above if I understood correctly, I placed the line activating posted sheet in the VB editor's Immediate Window I see does not happen any action. Did I do it correctly not sure?

I am sure you did do it correctly. I did not think it would work for you, but I wanted to be sure. If it had worked, then there would have been a more compact solution available (two loops would have been eliminated). The following won't work with your version of Excel, but it runs fine in my version of Excel (XL2010). I am posting it for anyone reading this thread who has Excel 2003 or later who might want to see the technique I used to avoid those loops...

[table="width: 500"]
[tr]
	[td]Sub ExtractAndJoin()
  Dim R As Long, C As Long, LastRow As Long
  Dim Chars As String, Pattern As String
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  For R = 6 To LastRow
    Chars = Join(WorksheetFunction.Index(Range("C" & R & ":P" & R).Value, 1, 0), "")
    Pattern = Join(WorksheetFunction.Index(Evaluate(Replace("IF({1},REPT(""@"",R#:Z#)&"" "")", "#", R)), 1, 0), "")
    Cells(R, "AB").Resize(, 9) = Split(Format(Trim(Chars), Pattern))
  Next
End Sub[/td]
[/tr]
[/table]

 

[Kishan]

I am sure you did do it correctly. I did not think it would work for you, but I wanted to be sure. If it had worked, then there would have been a more compact solution available (two loops would have been eliminated). The following won't work with your version of Excel, but it runs fine in my version of Excel (XL2010). I am posting it for anyone reading this thread who has Excel 2003 or later who might want to see the technique I used to avoid those loops...

Hi Rick, just wanted to let you know code you posted in the post#4 worked smooth with my version 2000, no problem at all. So I did adapt this the last one without the loops.

Thank you

Kind regards,
Kishan