| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically merge mulitiple cells to one cells
Any one can help me out here? Preferably without VB.
I have a bunch of continues cells in one column, and want to merge them into one cell and insert ";" in the between. The number of cells will be dynamic. Like A1 8R1234 A2 8R1235 A3 8R1236 A4 8R1237 A5 8R1238 .... Result cell = 8R1234;8R1235;8R1236;8R1237;8R1238;... As the result will be used in another function, I do not want to use one spare column beside the data to build up that text string one by one. Any advice? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Automatically merge mulitiple cells to one cells
You will have to use a UDF or an Add-In to acheive this. Try this UDF (User
Defined function). From workbook launch VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. =CONCATRANGE(A1:A5,";") Function CONCATRANGE(varRange As Range, Optional varDelimiter As String) Dim varTemp As Range For Each varTemp In varRange CONCATRANGE = CONCATRANGE & varDelimiter & varTemp Next If varDelimiter < vbNullString Then CONCATRANGE = Mid(CONCATRANGE, 2) End Function If this post helps click Yes --------------- Jacob Skaria "Edward Wang" wrote: Any one can help me out here? Preferably without VB. I have a bunch of continues cells in one column, and want to merge them into one cell and insert ";" in the between. The number of cells will be dynamic. Like A1 8R1234 A2 8R1235 A3 8R1236 A4 8R1237 A5 8R1238 ... Result cell = 8R1234;8R1235;8R1236;8R1237;8R1238;... As the result will be used in another function, I do not want to use one spare column beside the data to build up that text string one by one. Any advice? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Automatically merge mulitiple cells to one cells
Thank you, Jacob! It works!
A further question, if I have another column beside to column A, like below. I want to merge the Text of Column A based on the condition of Column B. Say if I only Pick A from Column B, the result will be "8R1234;8R1236;8R1238" Column A Column B 8R1234 A 8R1235 B 8R1236 A 8R1237 B 8R1238 A Thanks in advance! "Jacob Skaria" wrote: You will have to use a UDF or an Add-In to acheive this. Try this UDF (User Defined function). From workbook launch VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. =CONCATRANGE(A1:A5,";") Function CONCATRANGE(varRange As Range, Optional varDelimiter As String) Dim varTemp As Range For Each varTemp In varRange CONCATRANGE = CONCATRANGE & varDelimiter & varTemp Next If varDelimiter < vbNullString Then CONCATRANGE = Mid(CONCATRANGE, 2) End Function If this post helps click Yes --------------- Jacob Skaria "Edward Wang" wrote: Any one can help me out here? Preferably without VB. I have a bunch of continues cells in one column, and want to merge them into one cell and insert ";" in the between. The number of cells will be dynamic. Like A1 8R1234 A2 8R1235 A3 8R1236 A4 8R1237 A5 8R1238 ... Result cell = 8R1234;8R1235;8R1236;8R1237;8R1238;... As the result will be used in another function, I do not want to use one spare column beside the data to build up that text string one by one. Any advice? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Automatically merge mulitiple cells to one cells
Try the below...(Taken from an earlier post of mine)
=CONCATBY($A$1:$B$5,A1,2) $A$1:$B$5 - Array A1 - lookup 2 - Column to be combined.. Try this UDF (User Defined function). From workbook launch VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. Function CONCATBY(varRange As Range, _ strData As String, intCol As Integer) Dim lngRow As Long For lngRow = 1 To varRange.Rows.Count If varRange(lngRow, 1) = strData Then CONCATBY = CONCATBY & "," & varRange(lngRow, intCol) End If Next CONCATBY = Mid(CONCATBY, 2) End Function If this post helps click Yes --------------- Jacob Skaria "Edward Wang" wrote: Thank you, Jacob! It works! A further question, if I have another column beside to column A, like below. I want to merge the Text of Column A based on the condition of Column B. Say if I only Pick A from Column B, the result will be "8R1234;8R1236;8R1238" Column A Column B 8R1234 A 8R1235 B 8R1236 A 8R1237 B 8R1238 A Thanks in advance! "Jacob Skaria" wrote: You will have to use a UDF or an Add-In to acheive this. Try this UDF (User Defined function). From workbook launch VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. =CONCATRANGE(A1:A5,";") Function CONCATRANGE(varRange As Range, Optional varDelimiter As String) Dim varTemp As Range For Each varTemp In varRange CONCATRANGE = CONCATRANGE & varDelimiter & varTemp Next If varDelimiter < vbNullString Then CONCATRANGE = Mid(CONCATRANGE, 2) End Function If this post helps click Yes --------------- Jacob Skaria "Edward Wang" wrote: Any one can help me out here? Preferably without VB. I have a bunch of continues cells in one column, and want to merge them into one cell and insert ";" in the between. The number of cells will be dynamic. Like A1 8R1234 A2 8R1235 A3 8R1236 A4 8R1237 A5 8R1238 ... Result cell = 8R1234;8R1235;8R1236;8R1237;8R1238;... As the result will be used in another function, I do not want to use one spare column beside the data to build up that text string one by one. Any advice? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Automatically merge mulitiple cells to one cells
it works! Thanks!
I just made two minimum changes: 1. using ";" in the between 2. using "instr" function to replace "=" logical judgement "Jacob Skaria" wrote: Try the below...(Taken from an earlier post of mine) =CONCATBY($A$1:$B$5,A1,2) $A$1:$B$5 - Array A1 - lookup 2 - Column to be combined.. Try this UDF (User Defined function). From workbook launch VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. Function CONCATBY(varRange As Range, _ strData As String, intCol As Integer) Dim lngRow As Long For lngRow = 1 To varRange.Rows.Count If varRange(lngRow, 1) = strData Then CONCATBY = CONCATBY & "," & varRange(lngRow, intCol) End If Next CONCATBY = Mid(CONCATBY, 2) End Function If this post helps click Yes --------------- Jacob Skaria "Edward Wang" wrote: Thank you, Jacob! It works! A further question, if I have another column beside to column A, like below. I want to merge the Text of Column A based on the condition of Column B. Say if I only Pick A from Column B, the result will be "8R1234;8R1236;8R1238" Column A Column B 8R1234 A 8R1235 B 8R1236 A 8R1237 B 8R1238 A Thanks in advance! "Jacob Skaria" wrote: You will have to use a UDF or an Add-In to acheive this. Try this UDF (User Defined function). From workbook launch VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. =CONCATRANGE(A1:A5,";") Function CONCATRANGE(varRange As Range, Optional varDelimiter As String) Dim varTemp As Range For Each varTemp In varRange CONCATRANGE = CONCATRANGE & varDelimiter & varTemp Next If varDelimiter < vbNullString Then CONCATRANGE = Mid(CONCATRANGE, 2) End Function If this post helps click Yes --------------- Jacob Skaria "Edward Wang" wrote: Any one can help me out here? Preferably without VB. I have a bunch of continues cells in one column, and want to merge them into one cell and insert ";" in the between. The number of cells will be dynamic. Like A1 8R1234 A2 8R1235 A3 8R1236 A4 8R1237 A5 8R1238 ... Result cell = 8R1234;8R1235;8R1236;8R1237;8R1238;... As the result will be used in another function, I do not want to use one spare column beside the data to build up that text string one by one. Any advice? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Automatically merge mulitiple cells to one cells
Hi, Download and install the following addin http://www.download.com/Morefunc/300...-10423159.html and then use the MCONCAT() function =MCONCAT(A1:A5,";") -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Edward Wang" wrote in message ... Any one can help me out here? Preferably without VB. I have a bunch of continues cells in one column, and want to merge them into one cell and insert ";" in the between. The number of cells will be dynamic. Like A1 8R1234 A2 8R1235 A3 8R1236 A4 8R1237 A5 8R1238 ... Result cell = 8R1234;8R1235;8R1236;8R1237;8R1238;... As the result will be used in another function, I do not want to use one spare column beside the data to build up that text string one by one. Any advice? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Merge cells | Excel Discussion (Misc queries) | |||
| how do I merge cells into one then delete the original cells? | Excel Worksheet Functions | |||
| mulitiple cells in a drop down | Excel Discussion (Misc queries) | |||
| Merge Cells | Excel Discussion (Misc queries) | |||
| How do I merge cells in Excel, like just 2 cells to make one big . | Excel Discussion (Misc queries) | |||
Hybrid Mode
