×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Concatenation of text strings

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!


Tunalover

RE: Concatenation of text strings

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"

RE: Concatenation of text strings

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.

RE: Concatenation of text strings

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:

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources