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