| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Another Stingconcat question
Sorry it toook so long to get back,
Hope you guys get this, but I have tyhe STRING CONCAT VBA installed and it works great witha =STRINCONCAT(a1:a100,"; ") you know it considates the whole range with ; in one cell I copy and pasted the vlookup_concat VBA and no matter how I plug in the the actual formula it will only produce one email address from the array What I find strang is the email address it produces is actually the second in series =VLOOKUP_CONCAT(C13:F500,"Dallas",4,"; ") is only producing one of the email address in specified range So if all my office "names" are in col C and the email are in Col F Say there is Dallas, Austin and Seatlle, 100 rows down, and F has ind. email address FYI Col C is sorted A-Z =StringContcat(",",IF($C$13:$C$500="Katy",$F$12:$F $500,"")) this is the other suggestion with eith String concat, same result only one email address, and I did crtl shft enter too btw please advise These are the two VBA codes installed Function VLOOKUP_CONCAT(rngRange As Range, _ strLookupValue As String, intColumn As Integer, _ Optional strDelimiter As String = " ") Dim lngRow As Long For lngRow = 1 To rngRange.Rows.Count If CStr(rngRange(lngRow, 1)) = strLookupValue Then _ VLOOKUP_CONCAT = VLOOKUP_CONCAT & strDelimiter & _ rngRange(lngRow, intColumn) Next VLOOKUP_CONCAT = Mid(VLOOKUP_CONCAT, len(strDelimiter)+1) End Function You would need to add a new function to do this. Are you ok with using the VBEditor? Press Alt-F11 Click Insert Module Paste in this code (sorry, it's a little long, be sure you get it all): =========== Function StringConcat(Sep As String, ParamArray Args()) As String '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''' ' StringConcat ' This function concatenates all the elements in the Args array, ' delimited by the Sep character, into a single string. This function ' can be used in an array formula. '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''' Dim S As String Dim N As Long Dim M As Long Dim R As Range Dim NumDims As Long Dim LB As Long Dim IsArrayAlloc As Boolean ''''''''''''''''''''''''''''''''''''''''''' ' If no parameters were passed in, return ' vbNullString. ''''''''''''''''''''''''''''''''''''''''''' If UBound(Args) - LBound(Args) + 1 = 0 Then StringConcat = vbNullString Exit Function End If For N = LBound(Args) To UBound(Args) '''''''''''''''''''''''''''''''''''''''''''''''' ' Loop through the Args '''''''''''''''''''''''''''''''''''''''''''''''' If IsObject(Args(N)) = True Then ''''''''''''''''''''''''''''''''''''' ' OBJECT ' If we have an object, ensure it ' it a Range. The Range object ' is the only type of object we'll ' work with. Anything else causes ' a #VALUE error. '''''''''''''''''''''''''''''''''''' If TypeOf Args(N) Is Excel.Range Then ''''''''''''''''''''''''''''''''''''''''' ' If it is a Range, loop through the ' cells and create append the elements ' to the string S. ''''''''''''''''''''''''''''''''''''''''' For Each R In Args(N).Cells S = S & R.Text & Sep Next R Else ''''''''''''''''''''''''''''''''' ' Unsupported object type. Return ' a #VALUE error. ''''''''''''''''''''''''''''''''' StringConcat = CVErr(xlErrValue) Exit Function End If ElseIf IsArray(Args(N)) = True Then On Error Resume Next ''''''''''''''''''''''''''''''''''''' ' ARRAY ' If Args(N) is an array, ensure it ' is an allocated array. ''''''''''''''''''''''''''''''''''''' IsArrayAlloc = (Not IsError(LBound(Args(N))) And _ (LBound(Args(N)) <= UBound(Args(N)))) On Error GoTo 0 If IsArrayAlloc = True Then '''''''''''''''''''''''''''''''''''' ' The array is allocated. Determine ' the number of dimensions of the ' array. ''''''''''''''''''''''''''''''''''''' NumDims = 1 On Error Resume Next Err.Clear NumDims = 1 Do Until Err.Number < 0 LB = LBound(Args(N), NumDims) If Err.Number = 0 Then NumDims = NumDims + 1 Else NumDims = NumDims - 1 End If Loop '''''''''''''''''''''''''''''''''' ' The array must have either ' one or two dimensions. Greater ' that two caues a #VALUE error. '''''''''''''''''''''''''''''''''' If NumDims 2 Then StringConcat = CVErr(xlErrValue) Exit Function End If If NumDims = 1 Then For M = LBound(Args(N)) To UBound(Args(N)) If Args(N)(M) < vbNullString Then S = S & Args(N)(M) & Sep End If Next M Else For M = LBound(Args(N), 1) To UBound(Args(N), 1) If Args(N)(M, 1) < vbNullString Then S = S & Args(N)(M, 1) & Sep End If Next M For M = LBound(Args(N), 2) To UBound(Args(N), 2) If Args(N)(M, 2) < vbNullString Then S = S & Args(N)(M, 2) & Sep End If Next M End If Else S = S & Args(N) & Sep End If Else S = S & Args(N) & Sep End If Next N ''''''''''''''''''''''''''''''''''' ' Remove the trailing Sep character ''''''''''''''''''''''''''''''''''' If Len(Sep) 0 Then S = Left(S, Len(S) - Len(Sep)) End If StringConcat = S End Function =========== The code is also available he http://www.cpearson.com/excel/stringconcatenation.aspx Press Alt-Q to close the editor and save your sheet. You've just added a function called StringConcat() to your sheet and it works very simply. If your 1000 cells are range A1:A1000, use this formula in another cell: =StringConcat(", ",A1:A1000) Voila! Works like a charm. Will that work for you? -- "Actually, I AM a rocket scientist." -- JB |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Another Stingconcat question
The below formula entered nomally is supposed to lookup all values in Column
C which match 'Dallas' and concatenate the corresponding email address in Col F. If you mean you are able to return only one mail address check out whether the entries are 'Dallas' without any spaces or try out this in a fresh sheet with some dummy entries =VLOOKUP_CONCAT(C13:F500,"Dallas",4,"; ") If this post helps click Yes --------------- Jacob Skaria " wrote: Sorry it toook so long to get back, Hope you guys get this, but I have tyhe STRING CONCAT VBA installed and it works great witha =STRINCONCAT(a1:a100,"; ") you know it considates the whole range with ; in one cell I copy and pasted the vlookup_concat VBA and no matter how I plug in the the actual formula it will only produce one email address from the array What I find strang is the email address it produces is actually the second in series =VLOOKUP_CONCAT(C13:F500,"Dallas",4,"; ") is only producing one of the email address in specified range So if all my office "names" are in col C and the email are in Col F Say there is Dallas, Austin and Seatlle, 100 rows down, and F has ind. email address FYI Col C is sorted A-Z =StringContcat(",",IF($C$13:$C$500="Katy",$F$12:$F $500,"")) this is the other suggestion with eith String concat, same result only one email address, and I did crtl shft enter too btw please advise These are the two VBA codes installed Function VLOOKUP_CONCAT(rngRange As Range, _ strLookupValue As String, intColumn As Integer, _ Optional strDelimiter As String = " ") Dim lngRow As Long For lngRow = 1 To rngRange.Rows.Count If CStr(rngRange(lngRow, 1)) = strLookupValue Then _ VLOOKUP_CONCAT = VLOOKUP_CONCAT & strDelimiter & _ rngRange(lngRow, intColumn) Next VLOOKUP_CONCAT = Mid(VLOOKUP_CONCAT, len(strDelimiter)+1) End Function You would need to add a new function to do this. Are you ok with using the VBEditor? Press Alt-F11 Click Insert Module Paste in this code (sorry, it's a little long, be sure you get it all): =========== Function StringConcat(Sep As String, ParamArray Args()) As String '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''' ' StringConcat ' This function concatenates all the elements in the Args array, ' delimited by the Sep character, into a single string. This function ' can be used in an array formula. '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''' Dim S As String Dim N As Long Dim M As Long Dim R As Range Dim NumDims As Long Dim LB As Long Dim IsArrayAlloc As Boolean ''''''''''''''''''''''''''''''''''''''''''' ' If no parameters were passed in, return ' vbNullString. ''''''''''''''''''''''''''''''''''''''''''' If UBound(Args) - LBound(Args) + 1 = 0 Then StringConcat = vbNullString Exit Function End If For N = LBound(Args) To UBound(Args) '''''''''''''''''''''''''''''''''''''''''''''''' ' Loop through the Args '''''''''''''''''''''''''''''''''''''''''''''''' If IsObject(Args(N)) = True Then ''''''''''''''''''''''''''''''''''''' ' OBJECT ' If we have an object, ensure it ' it a Range. The Range object ' is the only type of object we'll ' work with. Anything else causes ' a #VALUE error. '''''''''''''''''''''''''''''''''''' If TypeOf Args(N) Is Excel.Range Then ''''''''''''''''''''''''''''''''''''''''' ' If it is a Range, loop through the ' cells and create append the elements ' to the string S. ''''''''''''''''''''''''''''''''''''''''' For Each R In Args(N).Cells S = S & R.Text & Sep Next R Else ''''''''''''''''''''''''''''''''' ' Unsupported object type. Return ' a #VALUE error. ''''''''''''''''''''''''''''''''' StringConcat = CVErr(xlErrValue) Exit Function End If ElseIf IsArray(Args(N)) = True Then On Error Resume Next ''''''''''''''''''''''''''''''''''''' ' ARRAY ' If Args(N) is an array, ensure it ' is an allocated array. ''''''''''''''''''''''''''''''''''''' IsArrayAlloc = (Not IsError(LBound(Args(N))) And _ (LBound(Args(N)) <= UBound(Args(N)))) On Error GoTo 0 If IsArrayAlloc = True Then '''''''''''''''''''''''''''''''''''' ' The array is allocated. Determine ' the number of dimensions of the ' array. ''''''''''''''''''''''''''''''''''''' NumDims = 1 On Error Resume Next Err.Clear NumDims = 1 Do Until Err.Number < 0 LB = LBound(Args(N), NumDims) If Err.Number = 0 Then NumDims = NumDims + 1 Else NumDims = NumDims - 1 End If Loop '''''''''''''''''''''''''''''''''' ' The array must have either ' one or two dimensions. Greater ' that two caues a #VALUE error. '''''''''''''''''''''''''''''''''' If NumDims 2 Then StringConcat = CVErr(xlErrValue) Exit Function End If If NumDims = 1 Then For M = LBound(Args(N)) To UBound(Args(N)) If Args(N)(M) < vbNullString Then S = S & Args(N)(M) & Sep End If Next M Else For M = LBound(Args(N), 1) To UBound(Args(N), 1) If Args(N)(M, 1) < vbNullString Then S = S & Args(N)(M, 1) & Sep End If Next M For M = LBound(Args(N), 2) To UBound(Args(N), 2) If Args(N)(M, 2) < vbNullString Then S = S & Args(N)(M, 2) & Sep End If Next M End If Else S = S & Args(N) & Sep End If Else S = S & Args(N) & Sep End If Next N ''''''''''''''''''''''''''''''''''' ' Remove the trailing Sep character ''''''''''''''''''''''''''''''''''' If Len(Sep) 0 Then S = Left(S, Len(S) - Len(Sep)) End If StringConcat = S End Function =========== The code is also available he http://www.cpearson.com/excel/stringconcatenation.aspx Press Alt-Q to close the editor and save your sheet. You've just added a function called StringConcat() to your sheet and it works very simply. If your 1000 cells are range A1:A1000, use this formula in another cell: =StringConcat(", ",A1:A1000) Voila! Works like a charm. Will that work for you? -- "Actually, I AM a rocket scientist." -- JB |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Another Stingconcat question
hmmmmm now I am getting a #name
Simply Col A Col B Dallas 1234 Dallas 1234 Austin 1235 Austin 1235 formula =VLOOKUP_CONCAT(A1:B4,"Dallas",2,"; ") all cell are formated as 'General" no spaces ect. "Jacob Skaria" wrote: The below formula entered nomally is supposed to lookup all values in Column C which match 'Dallas' and concatenate the corresponding email address in Col F. If you mean you are able to return only one mail address check out whether the entries are 'Dallas' without any spaces or try out this in a fresh sheet with some dummy entries =VLOOKUP_CONCAT(C13:F500,"Dallas",4,"; ") If this post helps click Yes --------------- Jacob Skaria " wrote: Sorry it toook so long to get back, Hope you guys get this, but I have tyhe STRING CONCAT VBA installed and it works great witha =STRINCONCAT(a1:a100,"; ") you know it considates the whole range with ; in one cell I copy and pasted the vlookup_concat VBA and no matter how I plug in the the actual formula it will only produce one email address from the array What I find strang is the email address it produces is actually the second in series =VLOOKUP_CONCAT(C13:F500,"Dallas",4,"; ") is only producing one of the email address in specified range So if all my office "names" are in col C and the email are in Col F Say there is Dallas, Austin and Seatlle, 100 rows down, and F has ind. email address FYI Col C is sorted A-Z =StringContcat(",",IF($C$13:$C$500="Katy",$F$12:$F $500,"")) this is the other suggestion with eith String concat, same result only one email address, and I did crtl shft enter too btw please advise These are the two VBA codes installed Function VLOOKUP_CONCAT(rngRange As Range, _ strLookupValue As String, intColumn As Integer, _ Optional strDelimiter As String = " ") Dim lngRow As Long For lngRow = 1 To rngRange.Rows.Count If CStr(rngRange(lngRow, 1)) = strLookupValue Then _ VLOOKUP_CONCAT = VLOOKUP_CONCAT & strDelimiter & _ rngRange(lngRow, intColumn) Next VLOOKUP_CONCAT = Mid(VLOOKUP_CONCAT, len(strDelimiter)+1) End Function You would need to add a new function to do this. Are you ok with using the VBEditor? Press Alt-F11 Click Insert Module Paste in this code (sorry, it's a little long, be sure you get it all): =========== Function StringConcat(Sep As String, ParamArray Args()) As String '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''' ' StringConcat ' This function concatenates all the elements in the Args array, ' delimited by the Sep character, into a single string. This function ' can be used in an array formula. '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''' Dim S As String Dim N As Long Dim M As Long Dim R As Range Dim NumDims As Long Dim LB As Long Dim IsArrayAlloc As Boolean ''''''''''''''''''''''''''''''''''''''''''' ' If no parameters were passed in, return ' vbNullString. ''''''''''''''''''''''''''''''''''''''''''' If UBound(Args) - LBound(Args) + 1 = 0 Then StringConcat = vbNullString Exit Function End If For N = LBound(Args) To UBound(Args) '''''''''''''''''''''''''''''''''''''''''''''''' ' Loop through the Args '''''''''''''''''''''''''''''''''''''''''''''''' If IsObject(Args(N)) = True Then ''''''''''''''''''''''''''''''''''''' ' OBJECT ' If we have an object, ensure it ' it a Range. The Range object ' is the only type of object we'll ' work with. Anything else causes ' a #VALUE error. '''''''''''''''''''''''''''''''''''' If TypeOf Args(N) Is Excel.Range Then ''''''''''''''''''''''''''''''''''''''''' ' If it is a Range, loop through the ' cells and create append the elements ' to the string S. ''''''''''''''''''''''''''''''''''''''''' For Each R In Args(N).Cells S = S & R.Text & Sep Next R Else ''''''''''''''''''''''''''''''''' ' Unsupported object type. Return ' a #VALUE error. ''''''''''''''''''''''''''''''''' StringConcat = CVErr(xlErrValue) Exit Function End If ElseIf IsArray(Args(N)) = True Then On Error Resume Next ''''''''''''''''''''''''''''''''''''' ' ARRAY ' If Args(N) is an array, ensure it ' is an allocated array. ''''''''''''''''''''''''''''''''''''' IsArrayAlloc = (Not IsError(LBound(Args(N))) And _ (LBound(Args(N)) <= UBound(Args(N)))) On Error GoTo 0 If IsArrayAlloc = True Then '''''''''''''''''''''''''''''''''''' ' The array is allocated. Determine ' the number of dimensions of the ' array. ''''''''''''''''''''''''''''''''''''' NumDims = 1 On Error Resume Next Err.Clear NumDims = 1 Do Until Err.Number < 0 LB = LBound(Args(N), NumDims) If Err.Number = 0 Then NumDims = NumDims + 1 Else NumDims = NumDims - 1 End If Loop '''''''''''''''''''''''''''''''''' ' The array must have either ' one or two dimensions. Greater ' that two caues a #VALUE error. '''''''''''''''''''''''''''''''''' If NumDims 2 Then StringConcat = CVErr(xlErrValue) Exit Function End If If NumDims = 1 Then For M = LBound(Args(N)) To UBound(Args(N)) If Args(N)(M) < vbNullString Then S = S & Args(N)(M) & Sep End If Next M Else For M = LBound(Args(N), 1) To UBound(Args(N), 1) If Args(N)(M, 1) < vbNullString Then S = S & Args(N)(M, 1) & Sep End If Next M For M = LBound(Args(N), 2) To UBound(Args(N), 2) If Args(N)(M, 2) < vbNullString Then S = S & Args(N)(M, 2) & Sep End If Next M End If Else S = S & Args(N) & Sep End If Else S = S & Args(N) & Sep End If Next N ''''''''''''''''''''''''''''''''''' ' Remove the trailing Sep character ''''''''''''''''''''''''''''''''''' If Len(Sep) 0 Then S = Left(S, Len(S) - Len(Sep)) End If StringConcat = S End Function =========== The code is also available he http://www.cpearson.com/excel/stringconcatenation.aspx Press Alt-Q to close the editor and save your sheet. You've just added a function called StringConcat() to your sheet and it works very simply. If your 1000 cells are range A1:A1000, use this formula in another cell: =StringConcat(", ",A1:A1000) Voila! Works like a charm. Will that work for you? -- "Actually, I AM a rocket scientist." -- JB |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Another Stingconcat question
That should bring the result 1234; 4567.. If that is #name error make sure
you have copied the function to a module within the same workbook. If this post helps click Yes --------------- Jacob Skaria " wrote: hmmmmm now I am getting a #name Simply Col A Col B Dallas 1234 Dallas 1234 Austin 1235 Austin 1235 formula =VLOOKUP_CONCAT(A1:B4,"Dallas",2,"; ") all cell are formated as 'General" no spaces ect. "Jacob Skaria" wrote: The below formula entered nomally is supposed to lookup all values in Column C which match 'Dallas' and concatenate the corresponding email address in Col F. If you mean you are able to return only one mail address check out whether the entries are 'Dallas' without any spaces or try out this in a fresh sheet with some dummy entries =VLOOKUP_CONCAT(C13:F500,"Dallas",4,"; ") If this post helps click Yes --------------- Jacob Skaria " wrote: Sorry it toook so long to get back, Hope you guys get this, but I have tyhe STRING CONCAT VBA installed and it works great witha =STRINCONCAT(a1:a100,"; ") you know it considates the whole range with ; in one cell I copy and pasted the vlookup_concat VBA and no matter how I plug in the the actual formula it will only produce one email address from the array What I find strang is the email address it produces is actually the second in series =VLOOKUP_CONCAT(C13:F500,"Dallas",4,"; ") is only producing one of the email address in specified range So if all my office "names" are in col C and the email are in Col F Say there is Dallas, Austin and Seatlle, 100 rows down, and F has ind. email address FYI Col C is sorted A-Z =StringContcat(",",IF($C$13:$C$500="Katy",$F$12:$F $500,"")) this is the other suggestion with eith String concat, same result only one email address, and I did crtl shft enter too btw please advise These are the two VBA codes installed Function VLOOKUP_CONCAT(rngRange As Range, _ strLookupValue As String, intColumn As Integer, _ Optional strDelimiter As String = " ") Dim lngRow As Long For lngRow = 1 To rngRange.Rows.Count If CStr(rngRange(lngRow, 1)) = strLookupValue Then _ VLOOKUP_CONCAT = VLOOKUP_CONCAT & strDelimiter & _ rngRange(lngRow, intColumn) Next VLOOKUP_CONCAT = Mid(VLOOKUP_CONCAT, len(strDelimiter)+1) End Function You would need to add a new function to do this. Are you ok with using the VBEditor? Press Alt-F11 Click Insert Module Paste in this code (sorry, it's a little long, be sure you get it all): =========== Function StringConcat(Sep As String, ParamArray Args()) As String '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''' ' StringConcat ' This function concatenates all the elements in the Args array, ' delimited by the Sep character, into a single string. This function ' can be used in an array formula. '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''' Dim S As String Dim N As Long Dim M As Long Dim R As Range Dim NumDims As Long Dim LB As Long Dim IsArrayAlloc As Boolean ''''''''''''''''''''''''''''''''''''''''''' ' If no parameters were passed in, return ' vbNullString. ''''''''''''''''''''''''''''''''''''''''''' If UBound(Args) - LBound(Args) + 1 = 0 Then StringConcat = vbNullString Exit Function End If For N = LBound(Args) To UBound(Args) '''''''''''''''''''''''''''''''''''''''''''''''' ' Loop through the Args '''''''''''''''''''''''''''''''''''''''''''''''' If IsObject(Args(N)) = True Then ''''''''''''''''''''''''''''''''''''' ' OBJECT ' If we have an object, ensure it ' it a Range. The Range object ' is the only type of object we'll ' work with. Anything else causes ' a #VALUE error. '''''''''''''''''''''''''''''''''''' If TypeOf Args(N) Is Excel.Range Then ''''''''''''''''''''''''''''''''''''''''' ' If it is a Range, loop through the ' cells and create append the elements ' to the string S. ''''''''''''''''''''''''''''''''''''''''' For Each R In Args(N).Cells S = S & R.Text & Sep Next R Else ''''''''''''''''''''''''''''''''' ' Unsupported object type. Return ' a #VALUE error. ''''''''''''''''''''''''''''''''' StringConcat = CVErr(xlErrValue) Exit Function End If ElseIf IsArray(Args(N)) = True Then On Error Resume Next ''''''''''''''''''''''''''''''''''''' ' ARRAY ' If Args(N) is an array, ensure it ' is an allocated array. ''''''''''''''''''''''''''''''''''''' IsArrayAlloc = (Not IsError(LBound(Args(N))) And _ (LBound(Args(N)) <= UBound(Args(N)))) On Error GoTo 0 If IsArrayAlloc = True Then '''''''''''''''''''''''''''''''''''' ' The array is allocated. Determine ' the number of dimensions of the ' array. ''''''''''''''''''''''''''''''''''''' NumDims = 1 On Error Resume Next Err.Clear NumDims = 1 Do Until Err.Number < 0 LB = LBound(Args(N), NumDims) If Err.Number = 0 Then NumDims = NumDims + 1 Else NumDims = NumDims - 1 End If Loop '''''''''''''''''''''''''''''''''' ' The array must have either ' one or two dimensions. Greater ' that two caues a #VALUE error. '''''''''''''''''''''''''''''''''' If NumDims 2 Then StringConcat = CVErr(xlErrValue) Exit Function End If If NumDims = 1 Then For M = LBound(Args(N)) To UBound(Args(N)) If Args(N)(M) < vbNullString Then S = S & Args(N)(M) & Sep End If Next M Else For M = LBound(Args(N), 1) To UBound(Args(N), 1) If Args(N)(M, 1) < vbNullString Then S = S & Args(N)(M, 1) & Sep End If Next M For M = LBound(Args(N), 2) To UBound(Args(N), 2) If Args(N)(M, 2) < vbNullString Then S = S & Args(N)(M, 2) & Sep End If Next M End If Else S = S & Args(N) & Sep End If Else S = S & Args(N) & Sep End If Next N ''''''''''''''''''''''''''''''''''' ' Remove the trailing Sep character ''''''''''''''''''''''''''''''''''' If Len(Sep) 0 Then S = Left(S, Len(S) - Len(Sep)) End If StringConcat = S End Function =========== The code is also available he http://www.cpearson.com/excel/stringconcatenation.aspx Press Alt-Q to close the editor and save your sheet. You've just added a function called StringConcat() to your sheet and it works very simply. If your 1000 cells are range A1:A1000, use this formula in another cell: =StringConcat(", ",A1:A1000) Voila! Works like a charm. Will that work for you? -- "Actually, I AM a rocket scientist." -- JB |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How 2 Question | Excel Discussion (Misc queries) | |||
| where can I see my question and answer? Yesterday I ask a question | Excel Discussion (Misc queries) | |||
| Another Question | Excel Worksheet Functions | |||
| Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
| The question is an excel question that I need to figure out howto do in excel. | Excel Worksheet Functions | |||
Hybrid Mode
