Concatenation of text strings

[Tunalover]

Folks-
At work I have frequent occasion to take a long column of cells containing random text strings and append (concatenate) a text string to each cell in that column. Years ago a guy showed me an amazing and easy way to do this in MS Excel without using a built-in function. Does anyone know of this method? This time I'm going to write it down!




Tunalover

 

[MintJulep]

if you have

A B C
1 one two three

then

=A1&B1&C1 will return you "onetwothree"

If you need spaces they either need to be in the referenced cells, or you can add them:

=A1&" "&B1 returns "one two"

 

[jghrist]

If your text is in A1:A20 and you want to concatenate the string "ABC" to each cell:

1. Put =A1&"ABC" in cell B1.
2. Copy cell B1 to B2:B20.
3. Highlight B1:B20, then Edit|Copy (or ctrl-C)
4. Move to A1 and Edit|Paste Special|Values.
5. Delete column B.

 

[nhebb]

A more advanced solution would be to open up the macro editor, right-click on the Personal.xls VBA project and select Insert > Module. Rename the module if you like. Then, add the following code to the module:

Public Sub AppendTextToRange()

    Dim rng As Range
    Dim c As Range
    Dim txt As String
    
    ' trim out leading and trailing spaces
    ' Note: remove the trim function if you want to be
    '       able to append spaces to text
    txt = Trim$(InputBox("Enter text to append", "Text to append"))
    If LenB(txt) = 0 Then
        MsgBox "No text was entered. Action cancelled"
        Exit Sub
    End If
    
    ' turn off error handling - input box throws an error if user clicks cancel
    On Error Resume Next
    Set rng = Application.InputBox("Select the range of cells to append to ...", _
                                   "Select range", "A1", , , , , 8)
    If Err.Number <> 0 Then
        MsgBox "No range was selected was entered. Action cancelled"
        Exit Sub
    End If
    
    ' switch to txt & c.value if you want to prepend text
    For Each c In rng
        c.Value = c.Value & txt
    Next
    
    Set c = Nothing
    Set rng = Nothing

End Sub

After pasting the code, right-click on any toolbar and select Customize. In the Commands tab, scroll down the categories list and select Macros. Drag the Custom Button smiley face button to the toolbar.

You can then right click on the new button to (1) assign the macro you just created and (2) change the image to another built-in one or edit the image manually.

Hope that helps,

Nick Hebb