| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Text to column and selecting values based on a different column | Excel Worksheet Functions | |||
| Text to column and selecting values based on a different column | Excel Discussion (Misc queries) | |||
| Lookup Data in two seperate Spreadsheets | Excel Worksheet Functions | |||
| Column picked randomly with probability relative to number of entr | Excel Worksheet Functions | |||
| selecting values from raw data | Excel Worksheet Functions | |||
Linear Mode
