Concatenation of text strings
Concatenation of text strings
(OP)
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!
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





RE: Concatenation of text strings
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"
RE: Concatenation of text strings
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.
RE: Concatenation of text strings
CODE
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