insert text into cell already containing text

[DanielRogers]

I have a column where each cell contains a word or phrase. I want to be able to insert a word in each cell of that column before the text that it already contains. I would also like to be able to insert a word after the text the cells in that column already contain.
A2 "marketing"
A3 "advertising"
A4 "social media"
I would like to be able to insert the "best " or "top " before the text in each cell in that column and insert " agency" or " firm" after the text in each cell.
Currently I am typing "1 marketing 2" "1 advertising 2" "1 social media 2" then copy and paste and replace 1 with best. replace 2 with agency. Is there solution? The copy paste way takes a long time as I am working with a long list. Thanks for any help. I am using 2007 excel. Windows OS

 

[MrKowz]

Welcome to the forums!

I don't quite think I understand what you're doing here... but here is something that might be of assistance:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Prefix</td><td style=";">Best</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Suffix</td><td style=";">Firm</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Best marketing Firm</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Best advertising Firm</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Best social media Firm</td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A4</th><td style="text-align:left">=$B$1&" marketing "&$B$2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A5</th><td style="text-align:left">=$B$1&" advertising "&$B$2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A6</th><td style="text-align:left">=$B$1&" social media "&$B$2</td></tr></tbody></table></td></tr></table><br />

And you can copy/paste special/values on the data the formulas create.

 

[JackDanIce]

Welcome to the board

Think easiest is type in the following formula:

="Best "&A1&" agency"

Drag it down to your last row, then copy and paste over your exisiting column as values only

 

[VoG]

Try with a macro. Press ALT + F11 to open the Visual Basic Editor, select Module from the Insert menu and paste into the white space on the right

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("A" & i)
        .Value = "Best " & .Value & " firm"
    End With
Next i
End Sub

Press ALT + Q to close the code window, press ALT + F8, select test then click the Run button.

 

[JamesW]

Hi there,

Try:

Sub test()
    lRow = Range("A1").End(xlDown).Row
    For i = 2 To lRow
        Cells(i, 1).Value = "best " & Cells(i, 1).Value & " agency"
    Next
End Sub

How do you want to mix up the best/top and agency/firm values?

 

[DanielRogers]

Hi there,

Try:

Sub test()
    lRow = Range("A1").End(xlDown).Row
    For i = 2 To lRow
        Cells(i, 1).Value = "best " & Cells(i, 1).Value & " agency"
    Next
End Sub

How do you want to mix up the best/top and agency/firm values?

I am trying to create long keyword lists for PPC advertising. start with three keywords "marketing" "advertising" "social media" and end up with
marketing agency
marketing
advertising
social media
advertising agency
social media agency
best marketing agency
best advertising agency
best social media agency
marketing firm
advertising firm
social media firm
best marketing firm
best advertising firm
best social media firm

then top, company, companies, agencies, service, services, then once that list is compiled then throw buy in front of all of them.

The idea is to be able to grow the list making long tail keywords without removing any shorter ones that have been previously created. The longer list I am able to formulate the more competitive I can be.

 

[MrKowz]

Perhaps something like:

Public Sub DanielRogers()
Dim PrefixArray     As Variant, _
    SuffixArray     As Variant, _
    KeywordArray    As Variant, _
    i               As Long, _
    j               As Long, _
    k               As Long
    
PrefixArray = Array("", "Best ", "Top ")
SuffixArray = Array("", " Agency ", " Firm")
KeywordArray = Array("Marketing", "Advertising", "Social Media")
    
For i = 0 To UBound(KeywordArray)
    For j = 0 To UBound(SuffixArray)
        For k = 0 To UBound(PrefixArray)
            Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Application.Trim(PrefixArray(k) & KeywordArray(i) & SuffixArray(j))
        Next k
    Next j
Next i
End Sub

 

[DanielRogers]

Welcome to the board

Think easiest is type in the following formula:

="Best "&A1&" agency"

Drag it down to your last row, then copy and paste over your exisiting column as values only

Thanks this worked. I am not very experienced with macros and some of the other answers were a bit over my head. This still requires quite a bit of copy and pasting so I'm sure there is a faster way but this is better that what I was doing.