insert text from one cell into part of another

[John Caines]

Hello All.
I'm trying to figure out how to insert whatever text is in cell A2 and combine this with text that is already in B2, so it kind of compiles it all into cell C2.
Here's an example (As I haven't explained that too good I feel!

Cell A 2 has a place name, let's say it is 'Bolton'
Cell B2 has about 35 words in it, but I need this word Bolton (Cell A2) to go in between the words, services in (then insert A2) and ? (a question mark)

So cell C2 would end up being;
Are you in need of website services in Bolton? Then contact xyz today on 12345678

Hope the above makes sense?
If someone can help me out here as I have to change about 1000!
Really don't want to do this manually

Hope it all makes sense.
I've tried googling, but I've got bogged down with lookups /match formulas etc, as I don't know how to do it, and I'm kind of going around in circles
Many thanks in advance
Hope someone can help here.
Best regards
John C

 

[jasonb75]

From your example, I think you need substitute

=SUBSTITUTE(B2,"services in","services in "&A2&"?")

Note that substitute is case sensitive, meaning that "services in" will not be found if the cell contains "Services in" or vice versa. There are alternatives that will work with both cases but I've started with the simplest method.

 

[sykes]

Hi

Bit schoolboy, but...

This assumes that the text in B2 is always the same, and that the placement of A2 is always in the same position i.e. after 39 characters...

test.xlsm
	A	B	C
2	Bolton	Are you in need of website services in ? Then contact xyz today on 12345678	Are you in need of website services in Bolton ? Then contact xyz today on 12345678
Spare2

Cell Formulas
Range	Formula
C2	C2	=LEFT(B2,39)&" "&A2&" "&RIGHT(B2,LEN(B2)-39)

 

[John Caines]

Hello Jasonb75 & sykes!
Many thanks for both your replies, brilliant stuff!

I've tried out both formulas and jasonb75, your formula is working perfectly for me.
This is really great stuff, as I can save this spreadsheet as a template and use again, as I know I'll come up against this problem many times in the future.

Really big thank you for this, very much appreciated.

And I also tried your code sykes.
Actually, the character count was / is 61, but it was a little more tricky as I needed the text to end up next to the question mark. I was having issues with this, and a double space.
But I do get the jist of what the code is trying to do sykes, but in this case, jasonb75 code is a bit more flexible for me. I can change this on the fly for other issues I'll face in the future a bit more easily.

Many thanks to you both for getting back to me on this.
Very much apreciated.

Have a great day the both of you!
Best regards
A very greatful
John C!

 

[mohadin]

Hi
Try

Sub test()
    Dim x
    Dim lr, i, ii
    With Sheets("sheet1")
        lr = .Cells(Rows.Count, 2).End(xlUp).Row
        For i = 2 To lr
            x = Split(Cells(i, 2), " ")
            For ii = 0 To UBound(x) - 1
                If UCase(x(ii)) = UCase("services") And UCase(x(ii + 1)) = UCase("in") Then
                    x(ii + 1) = x(ii + 1) & " " & Cells(i, 1) & "?"
                    Cells(i, 3) = Join(x, " ")
                End If
            Next: Next
    End With
End Sub

 

[John Caines]

Hello mohadin!
Many thanks for your VBA solution!

I have just tried it, and it worked fine!
Many thanks for taking the time to write this, it reminds me of some code a guy used to write here called Jindon,, not sure if he is still around, but your code looks like his style, crazy looking! LOL
I'll save this code also, as I can see from it I can replace the words or question mark if I have any other different content.
Many thanks again Mohadin for your reply

Best regards
A very grateful
John C

 

[mohadin]

Hi
You are very well come
And thank you for the compliment
To be honest with you I'm following Mr. Jindon cause I like his style so much
Thank you again
Be happy

 

[John Caines]

Hi Mohadin,
Thanks again,
& glad to here Mr.Jindon is still around!
He helped me so so much years ago with some crazy code, many different crazy looking vba codes infact.
A very clever coder.

Thanks again, and stay safe
Best regards Mohadin, and to Jindon (wherever he is!)
John C