Text to column and selecting values based on a different column
 

Is there a way to separate a column using Text to column (b with
multiple data in each row) and add content of another column i.e
change to 52 1222; 52 1442; 52 1477...?

A B
52 1222, 1442, 1477, 1722, 1777
52 1232, 1284, 1287, 1311, 1312, 1317, 1322, 1341,


Thanks for your assistance.

Text to column and selecting values based on a different column
 

Assumptions:

Each number within the text string is made up of 4 digits

Columns A and B contain the data, starting at Row 2

Formula:

C2, copied across and down:

=IF(COLUMNS($C2:C2)<=LEN(SUBSTITUTE(SUBSTITUTE($B2 ,",",""),"
",""))/4,$A2&" "&MID(SUBSTITUTE(SUBSTITUTE($B2,",",""),"
",""),COLUMNS($C2:C2)*4-4+1,4),"")

Hope this helps!

In article .com,
"torooo" wrote:

Is there a way to separate a column using Text to column (b with
multiple data in each row) and add content of another column i.e
change to 52 1222; 52 1442; 52 1477...?

A B
52 1222, 1442, 1477, 1722, 1777
52 1232, 1284, 1287, 1311, 1312, 1317, 1322, 1341,


Thanks for your assistance.

Text to column and selecting values based on a different column
 

Thanks Domenic for your assitance.

The numbers in Column B is not always 4 digits - it could be 2, 3, 4 or
more.


Domenic wrote:
Assumptions:

Each number within the text string is made up of 4 digits

Columns A and B contain the data, starting at Row 2

Formula:

C2, copied across and down:

=IF(COLUMNS($C2:C2)<=LEN(SUBSTITUTE(SUBSTITUTE($B2 ,",",""),"
",""))/4,$A2&" "&MID(SUBSTITUTE(SUBSTITUTE($B2,",",""),"
",""),COLUMNS($C2:C2)*4-4+1,4),"")

Hope this helps!

In article .com,
"torooo" wrote:

Is there a way to separate a column using Text to column (b with
multiple data in each row) and add content of another column i.e
change to 52 1222; 52 1442; 52 1477...?

A B
52 1222, 1442, 1477, 1722, 1777
52 1232, 1284, 1287, 1311, 1312, 1317, 1322, 1341,


Thanks for your assistance.

Text to column and selecting values based on a different column
 

Assuming that Column A and Column B contain the data, starting at Row 2,
try the following instead...

Select C2

Insert Name Define

Name: BreakString

Refers to:

=EVALUATE("{"""&SUBSTITUTE(Sheet1!$B2,",",""",""") &"""}")

Click Ok

Then, enter the following formula in C2, copy across, and down:

=IF(COLUMNS($C2:C2)<=COUNTA(BreakString),$A2&"
"&INDEX(BreakString,COLUMNS($C2:C2)),"")

Hope this helps!

In article om,
"torooo" wrote:

Thanks Domenic for your assitance.

The numbers in Column B is not always 4 digits - it could be 2, 3, 4 or
more.


Domenic wrote:
Assumptions:

Each number within the text string is made up of 4 digits

Columns A and B contain the data, starting at Row 2

Formula:

C2, copied across and down:

=IF(COLUMNS($C2:C2)<=LEN(SUBSTITUTE(SUBSTITUTE($B2 ,",",""),"
",""))/4,$A2&" "&MID(SUBSTITUTE(SUBSTITUTE($B2,",",""),"
",""),COLUMNS($C2:C2)*4-4+1,4),"")

Hope this helps!

In article .com,
"torooo" wrote:

Is there a way to separate a column using Text to column (b with
multiple data in each row) and add content of another column i.e
change to 52 1222; 52 1442; 52 1477...?

A B
52 1222, 1442, 1477, 1722, 1777
52 1232, 1284, 1287, 1311, 1312, 1317, 1322, 1341,


Thanks for your assistance.

Text to column and selecting values based on a different column
 

Thanks again for your response. The formula sorts of works - it
combines columns A and B.

I think I wasn't clear when I asked the question...I am trying to break
or seperate multple data in a singe cell (70, 71, 72, 75, 76, 77, 719
or 1232, 1284, 187, 13141, 131244, 1317, 1322, 1341) and add the
content of another column i.e

Becomes
A B
C D
52 1232, 1284, 1287, 1311, 1312, 1317, 1322, 1341 521232 521244
52 70, 71, 72, 75, 76, 77, 79


Thanks



Domenic wrote:
Assuming that Column A and Column B contain the data, starting at Row 2,
try the following instead...

Select C2

Insert Name Define

Name: BreakString

Refers to:

=EVALUATE("{"""&SUBSTITUTE(Sheet1!$B2,",",""",""") &"""}")

Click Ok

Then, enter the following formula in C2, copy across, and down:

=IF(COLUMNS($C2:C2)<=COUNTA(BreakString),$A2&"
"&INDEX(BreakString,COLUMNS($C2:C2)),"")

Hope this helps!

In article om,
"torooo" wrote:

Thanks Domenic for your assitance.

The numbers in Column B is not always 4 digits - it could be 2, 3, 4 or
more.


Domenic wrote:
Assumptions:

Each number within the text string is made up of 4 digits

Columns A and B contain the data, starting at Row 2

Formula:

C2, copied across and down:

=IF(COLUMNS($C2:C2)<=LEN(SUBSTITUTE(SUBSTITUTE($B2 ,",",""),"
",""))/4,$A2&" "&MID(SUBSTITUTE(SUBSTITUTE($B2,",",""),"
",""),COLUMNS($C2:C2)*4-4+1,4),"")

Hope this helps!

In article .com,
"torooo" wrote:

Is there a way to separate a column using Text to column (b with
multiple data in each row) and add content of another column i.e
change to 52 1222; 52 1442; 52 1477...?

A B
52 1222, 1442, 1477, 1722, 1777
52 1232, 1284, 1287, 1311, 1312, 1317, 1322, 1341,


Thanks for your assistance.

Text to column and selecting values based on a different column
 

Let's see if I understand you correctly...

A2 contains 52

B2 contains 1222, 1442, 1477, 1722, 1777

....and you'd like the formula to return the following values for C2:G2...

52 1222
52 1442
52 1477
52 1722
52 1777

Is this right? If so, the solution I provided you will return these
results. Note that it's important that you select C2 before defining
BreakString, since the reference to Row 2 is relative.

In article .com,
"torooo" wrote:

Thanks again for your response. The formula sorts of works - it
combines columns A and B.

I think I wasn't clear when I asked the question...I am trying to break
or seperate multple data in a singe cell (70, 71, 72, 75, 76, 77, 719
or 1232, 1284, 187, 13141, 131244, 1317, 1322, 1341) and add the
content of another column i.e

Becomes
A B
C D
52 1232, 1284, 1287, 1311, 1312, 1317, 1322, 1341 521232 521244
52 70, 71, 72, 75, 76, 77, 79


Thanks



Domenic wrote:
Assuming that Column A and Column B contain the data, starting at Row 2,
try the following instead...

Select C2

Insert Name Define

Name: BreakString

Refers to:

=EVALUATE("{"""&SUBSTITUTE(Sheet1!$B2,",",""",""") &"""}")

Click Ok

Then, enter the following formula in C2, copy across, and down:

=IF(COLUMNS($C2:C2)<=COUNTA(BreakString),$A2&"
"&INDEX(BreakString,COLUMNS($C2:C2)),"")

Hope this helps!

In article om,
"torooo" wrote:

Thanks Domenic for your assitance.

The numbers in Column B is not always 4 digits - it could be 2, 3, 4 or
more.


Domenic wrote:
Assumptions:

Each number within the text string is made up of 4 digits

Columns A and B contain the data, starting at Row 2

Formula:

C2, copied across and down:

=IF(COLUMNS($C2:C2)<=LEN(SUBSTITUTE(SUBSTITUTE($B2 ,",",""),"
",""))/4,$A2&" "&MID(SUBSTITUTE(SUBSTITUTE($B2,",",""),"
",""),COLUMNS($C2:C2)*4-4+1,4),"")

Hope this helps!

In article .com,
"torooo" wrote:

Is there a way to separate a column using Text to column (b with
multiple data in each row) and add content of another column i.e
change to 52 1222; 52 1442; 52 1477...?

A B
52 1222, 1442, 1477, 1722, 1777
52 1232, 1284, 1287, 1311, 1312, 1317, 1322, 1341,


Thanks for your assistance.