Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Concatenation

Posted on 2013-01-29
6
Medium Priority
?
277 Views
Last Modified: 2013-02-05
I wish to concatenate from cell A4 to cell UE4 without having to enter each cell into my formula.  How do I do this?
0
Comment
Question by:rgarzakavo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 38832716
Can you be more specific? Do you just want the value in A4 to appear in UE4? Or do you want the value in E4 to be appended to the end of whatever is in UE4 every time you change E4?

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 38832744
If you want the former then put this formula in UE4:

    =E4

If you want the latter then put this code in the worksheet's code module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Application.EnableEvents = False
    If Not Intersect(Target, [E4]) Is Nothing Then
        [UE4].Value = [UE4].Value & [E4]
    End If
    Application.EnableEvents = True

End Sub

Kevin
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38832774
Hi,  rgarzakavo.

Or do you want a string containing the concatenated values of the range A4:UE4? If so, the  macro below will return this value. So, add the macro to the file and use a formula like the following...
=join_range(A4:UE4)

The code is...
Option Explicit

Function Join_Range(xRange As Range, Optional xDelim As String) As String
Dim xArray As Variant

If Len(xDelim) > 1 Then xDelim = Left(xDelim, 1)

xArray = Application.Transpose(Application.Transpose(xRange))

Join_Range = Join(xArray, xDelim)

End Function

Open in new window

Regards,
Brian.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 23

Accepted Solution

by:
NBVC earned 2000 total points
ID: 38832778
You would need a UDF (user defined function)

Here is one I use:

Add this as a module in the VB editor (ALT+F11, Insert|Module)

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Open in new window


Then formula would be:

=aconcat(A4:EU4)

you can add a separator too very easily

=aconcat(A4:EU4,",")

adds a comma between each value.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 38832955
I missed that.

That's a lot of cells to concatenate into one cell. Why are you doing this? What is the purpose?

Kevin
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38833662
Depending on what features you need in the concatenation, this may be useful to you:

Function ConcRange(Substrings As Range, Optional Delim As String = "", _
    Optional AsDisplayed As Boolean = False, Optional SkipBlanks As Boolean = False, _
    Optional TrimSpaces As Boolean = False)
    
    ' Function by Patrick Matthews, Matt Vidas, and rberke
    ' Revised based on suggestions from Dave Brett and Mark Hutchinson based on comments
    ' at http://www.experts-exchange.com/A_7811.html
    
    ' Concatenates a range of cells, using an optional delimiter.  The concatenated
    ' strings may be either actual values (AsDisplayed=False) or displayed values.
    ' If SkipBlanks=True, blank cells or cells that evaluate to a zero-length string
    ' are skipped in the concatenation
    
    ' Substrings: the range of cells whose values/text you want to concatenate.  May be
    ' from a row, a column, or a "rectangular" range (1+ rows, 1+ columns).  To use
    ' multiple areas, enclose the various ranges in parentheses, e.g.:
    ' =ConcRange((A1:A10,Q14:Q17,Z200:Z300),";")
    
    ' Delimiter: the optional separator you want inserted between each item to be
    ' concatenated.  By default, the function will use a zero-length string as the
    ' delimiter (which is what Excel's CONCATENATE function does), but you can specify
    ' your own character(s).  (The Delimiter can be more than one character)
    
    ' AsDisplayed: for numeric values (includes currency but not dates), this controls
    ' whether the real value of the cell is used for concatenation, or the formatted
    ' displayed value.  Note for how dates are handled: if AsDisplayed is FALSE or omitted,
    ' dates will show up using whatever format you have selected in your regional settings
    ' for displaying dates.  If AsDisplayed=TRUE, dates will use the formatted displayed
    ' value

    ' SkipBlanks: Indicates whether the function should ignore blank cells in the Substrings
    ' range when it performs the concatenation.  If SkipBlanks=FALSE or is omitted, the function
    ' includes blank cells in the concatenation.  In the examples above, where NoBlanks=False,
    ' you will see "extra" delimiters in cases where the Substrings range has blank cells
    
    ' TrimSpaces: Indicates whether leading or trailing spaces are removed from substrings
    ' prior to concatenation.  Multiple internal spaces are ignored, as the VBA Trim() and
    ' not Excel's TRIM() is used.  Space trimming is performed against actual or displayed
    ' value, as determined by the AsDisplayed argument.  If SkipBlanks and TrimSpaces are
    ' both True, "blank" is determined based on trimmed value or displayed text
    
    Dim CLL As Range
    
    If AsDisplayed And SkipBlanks And TrimSpaces Then
        For Each CLL In Substrings.Cells
            If Trim(CLL.Text) <> "" Then
                ConcRange = ConcRange & (Delim & Trim(CLL.Text))
            End If
        Next
    ElseIf AsDisplayed And SkipBlanks And Not TrimSpaces Then
        For Each CLL In Substrings.Cells
            If CLL.Text <> "" Then
                ConcRange = ConcRange & (Delim & CLL.Text)
            End If
        Next
    ElseIf AsDisplayed And Not SkipBlanks And TrimSpaces Then
        For Each CLL In Substrings.Cells
            ConcRange = ConcRange & (Delim & Trim(CLL.Text))
        Next
    ElseIf AsDisplayed And Not SkipBlanks And Not TrimSpaces Then
        For Each CLL In Substrings.Cells
            ConcRange = ConcRange & (Delim & CLL.Text)
        Next
    ElseIf Not AsDisplayed And SkipBlanks And TrimSpaces Then
        For Each CLL In Substrings.Cells
            If Trim(CLL.Value) <> "" Then
                ConcRange = ConcRange & (Delim & Trim(CLL.Value))
            End If
        Next
    ElseIf Not AsDisplayed And SkipBlanks And Not TrimSpaces Then
        For Each CLL In Substrings.Cells
            If CLL.Value <> "" Then
                ConcRange = ConcRange & (Delim & CLL.Value)
            End If
        Next
    ElseIf Not AsDisplayed And Not SkipBlanks And TrimSpaces Then
        For Each CLL In Substrings.Cells
            ConcRange = ConcRange & (Delim & Trim(CLL.Value))
        Next
    Else
        For Each CLL In Substrings.Cells
            ConcRange = ConcRange & (Delim & CLL.Value)
        Next
    End If
        
    ConcRange = Mid$(ConcRange, Len(Delim) + 1)
    
End Function

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

While working, an annoying popup showing below will come and we cannot cancel or close it form the screen. The error message will come again and again.
There are many software programs on offer that will claim to magically speed up your computer. The best advice I can give you is to avoid them like the plague, because they will often cause far more problems than they solve. Try some of these "do it…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question