Concatenate certain values in an array

I

Irishswim07

New Member
Joined
May 1, 2008
Messages
7
I've got an array in an row that I'd like to write a formula for in a separate cell to concatenate certain cells in that array. The array contains blank cells as well. For example:

Array row:
ts01 ts02 ts03 "blank" <blank>ts05 ts06 ts07 ts08

In this case I'd like the formula to look at this array and concatenate something like this:
ts01-ts03, ts05-tsh08

I've got a formula written right now...

=CONCATENATE(IFERROR(INDEX(Array,MATCH("*",Array,0)),""),"-",IFERROR(INDEX(Array,MATCH("*",Array,-1)),""))

but that only returns the first and last value "ts01-ts08" and doesn't look for the blank cell.

Is there a way to look for that blank cell and do one concatenate of those values, and then do another one after that blank cell?</blank>
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
What is ts01, ts02, etc... the contents of cells in a range (if so, what is the range) or the cell addresses themselves (if so, the 0 does not belong in them)? In other words, what result do you want... the concatenated values in the cells or the concatenated cell addresses?
 
Upvote 0
Rick Rothstein said:
What is ts01, ts02, etc... the contents of cells in a range (if so, what is the range) or the cell addresses themselves (if so, the 0 does not belong in them)? In other words, what result do you want... the concatenated values in the cells or the concatenated cell addresses?
Click to expand...


ts01, ts02, etc are the contents of the cells in "Array". There are blank cells in this array because they are placeholders for contents to be entered at a later date.

I need to be able to get the cell address of the blank cell, and concatenate the cell contents both before it, and after it.
 
Upvote 0
Irishswim07 said:
ts01, ts02, etc are the contents of the cells in "Array".
Click to expand...
Are the cells in the array down in a single column or across in a single row?

Will each cell in the range contain the same beginning text, differing only in the ending number? Or can there be different beginning text that would have to be concatenated in dashed series separately?
 
Upvote 0
Rick Rothstein said:
Are the cells in the array down in a single column or across in a single row?

Will each cell in the range contain the same beginning text, differing only in the ending number? Or can there be different beginning text that would have to be concatenated in dashed series separately?
Click to expand...


The cells are in the array across a single row.

Each cell contains the same beginning text (either 2 or 3 letters) and then contains a numerical value. There will have to be text that gets concatenated in dashed series separately if a blank space is encountered.
 
Upvote 0
Okay, first off, you will not be able to so what you want with a standard Excel formula since the CONCATENATE function does not work the way normal people expect it to (it cannot process a range of cells, only individual cells/text strings). However, there is a VBA solution that will work... it involves creating a UDF user defined function which can be used in an Excel formula. Here is the code...

Code: 
Function SeriesConcat(Rng As Range) As String
  Dim X As Long, Cell As Range, Parts() As String, SubParts() As String
  For Each Cell In Rng
    If Len(Cell.Value) Then
      SeriesConcat = SeriesConcat & " " & Cell.Value
    ElseIf Len(Trim(SeriesConcat)) Then
      SeriesConcat = SeriesConcat & ", " & Cell.Value
    Else
      SeriesConcat = Cell.Value
    End If
  Next
  Parts = Split(Application.Trim(SeriesConcat), ", ")
  For X = 0 To UBound(Parts)
    SubParts = Split(Parts(X))
    If UBound(SubParts) > 0 Then
      Parts(X) = SubParts(0) & "-" & SubParts(UBound(SubParts))
    End If
  Next
  SeriesConcat = Join(Parts, ", ")
End Function


HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use SeriesConcat just like it was a built-in Excel function, simply pass it the range you want it to process. For example,

=SeriesConcat(C3:C9)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Upvote 0
Rick Rothstein said:
Code: 
Function SeriesConcat(Rng As Range) As String
  Dim X As Long, Cell As Range, Parts() As String, SubParts() As String
  For Each Cell In Rng
    If Len(Cell.Value) Then
      SeriesConcat = SeriesConcat & " " & Cell.Value
    ElseIf Len(Trim(SeriesConcat)) Then
      SeriesConcat = SeriesConcat & ", " & Cell.Value
    Else
      SeriesConcat = Cell.Value
    End If
  Next
  Parts = Split(Application.Trim(SeriesConcat), ", ")
  For X = 0 To UBound(Parts)
    SubParts = Split(Parts(X))
    If UBound(SubParts) > 0 Then
      Parts(X) = SubParts(0) & "-" & SubParts(UBound(SubParts))
    End If
  Next
  SeriesConcat = Join(Parts, ", ")
End Function
Click to expand...
On the off-chance you might have two or more blank cells in a row within the designated range, use this code instead of what I posted above...
Code: 
Function SeriesConcat(Rng As Range) As String
  Dim X As Long, Cell As Range, Parts() As String, SubParts() As String
  For Each Cell In Rng
    If Len(Cell.Value) Then
      SeriesConcat = SeriesConcat & " " & Cell.Value
    ElseIf Len(Trim(SeriesConcat)) Then
      SeriesConcat = SeriesConcat & ", " & Cell.Value
    Else
      SeriesConcat = Cell.Value
    End If
  Next
  Parts = Split(Application.Trim(SeriesConcat), ", ")
  For X = 0 To UBound(Parts)
    SubParts = Split(Parts(X))
    If UBound(SubParts) > 0 Then
      Parts(X) = SubParts(0) & "-" & SubParts(UBound(SubParts))
    End If
  Next
  SeriesConcat = Join(Parts, ", ")
  Do While InStr(SeriesConcat, ", , ")
    SeriesConcat = Replace(SeriesConcat, ", , ", ", ")
  Loop
End Function


HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use SeriesConcat just like it was a built-in Excel function, simply pass it the range you want it to process. For example,

=SeriesConcat(C3:C9)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.[/QUOTE]
 
Upvote 0
Rick Rothstein said:
On the off-chance you might have two or more blank cells in a row within the designated range, use this code instead of what I posted above...
Code: 
Function SeriesConcat(Rng As Range) As String
  Dim X As Long, Cell As Range, Parts() As String, SubParts() As String
  For Each Cell In Rng
    If Len(Cell.Value) Then
      SeriesConcat = SeriesConcat & " " & Cell.Value
    ElseIf Len(Trim(SeriesConcat)) Then
      SeriesConcat = SeriesConcat & ", " & Cell.Value
    Else
      SeriesConcat = Cell.Value
    End If
  Next
  Parts = Split(Application.Trim(SeriesConcat), ", ")
  For X = 0 To UBound(Parts)
    SubParts = Split(Parts(X))
    If UBound(SubParts) > 0 Then
      Parts(X) = SubParts(0) & "-" & SubParts(UBound(SubParts))
    End If
  Next
  SeriesConcat = Join(Parts, ", ")
  Do While InStr(SeriesConcat, ", , ")
    SeriesConcat = Replace(SeriesConcat, ", , ", ", ")
  Loop
End Function


HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use SeriesConcat just like it was a built-in Excel function, simply pass it the range you want it to process. For example,

=SeriesConcat(C3:C9)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
Click to expand...
[/QUOTE]


Wow that is awesome! I do, in fact have ranges with multiple blank spaces, so this code works great!

The only thing I am noticing is that at the end of the concatenated series, there is always ", ,"

For example the output of the function is "ts01-ts03, ts05-ts08, ," with the two extra commas at the end
 
Upvote 0


Wow that is awesome! I do, in fact have ranges with multiple blank spaces, so this code works great!

The only thing I am noticing is that at the end of the concatenated series, there is always ", ,"

For example the output of the function is "ts01-ts03, ts05-ts08, ," with the two extra commas at the end[/QUOTE]
That is happening because you are including blank cells past the last filled cell in the specified range. This code should take care of that problem...
Code: 
Function SeriesConcat(Rng As Range) As String
  Dim X As Long, Cell As Range, Parts() As String, SubParts() As String
  For Each Cell In Rng
    If Len(Cell.Value) Then
      SeriesConcat = SeriesConcat & " " & Cell.Value
    ElseIf Len(Trim(SeriesConcat)) Then
      SeriesConcat = SeriesConcat & ", " & Cell.Value
    Else
      SeriesConcat = Cell.Value
    End If
  Next
  Parts = Split(Application.Trim(SeriesConcat), ", ")
  For X = 0 To UBound(Parts)
    SubParts = Split(Parts(X))
    If UBound(SubParts) > 0 Then
      Parts(X) = SubParts(0) & "-" & SubParts(UBound(SubParts))
    End If
  Next
  SeriesConcat = Join(Parts, ", ")
  Do While InStr(SeriesConcat, ", , ")
    SeriesConcat = Replace(SeriesConcat, ", , ", ", ")
  Loop
  SeriesConcat = Replace(SeriesConcat, ", ,", "")
End Function
 
Upvote 0
Rick Rothstein said:
Wow that is awesome! I do, in fact have ranges with multiple blank spaces, so this code works great!

The only thing I am noticing is that at the end of the concatenated series, there is always ", ,"

For example the output of the function is "ts01-ts03, ts05-ts08, ," with the two extra commas at the end
Click to expand...
That is happening because you are including blank cells past the last filled cell in the specified range. This code should take care of that problem...
Code: 
Function SeriesConcat(Rng As Range) As String
  Dim X As Long, Cell As Range, Parts() As String, SubParts() As String
  For Each Cell In Rng
    If Len(Cell.Value) Then
      SeriesConcat = SeriesConcat & " " & Cell.Value
    ElseIf Len(Trim(SeriesConcat)) Then
      SeriesConcat = SeriesConcat & ", " & Cell.Value
    Else
      SeriesConcat = Cell.Value
    End If
  Next
  Parts = Split(Application.Trim(SeriesConcat), ", ")
  For X = 0 To UBound(Parts)
    SubParts = Split(Parts(X))
    If UBound(SubParts) > 0 Then
      Parts(X) = SubParts(0) & "-" & SubParts(UBound(SubParts))
    End If
  Next
  SeriesConcat = Join(Parts, ", ")
  Do While InStr(SeriesConcat, ", , ")
    SeriesConcat = Replace(SeriesConcat, ", , ", ", ")
  Loop
  SeriesConcat = Replace(SeriesConcat, ", ,", "")
End Function
[/QUOTE]

That's it! Thank you so much for your help!
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
XLookup Variable Return Array Needed Similar to Vlookup & Match or Index & Match
Replies
3
Views
412
Peter_SSs
Peter_SSs
J
  • Solved
INDEX/MATCH 2D Array.
Replies
9
Views
591
Fluff
Fluff
F
  • Question
Want to return a unique list of values from a column that contains an array formula
2
Replies
14
Views
391
Fluff
Fluff
B
  • Question
Reduce two formula commas into one
Replies
3
Views
209
Fluff
Fluff
M
  • Question
Index Match - Column Number
Replies
4
Views
248
Mandy_
M

Forum statistics

Threads
1,206,817
Messages
6,075,041
Members
446,114
Latest member
FadDak

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top