insert text from one cell into part of another

John Caines

Well-known Member
Joined
Aug 28, 2006
Messages
1,139
Office Version
  1. 2019
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,438
Office Version
  1. 365
Platform
  1. Windows
From your example, I think you need substitute
Excel Formula:
=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

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
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
ABC
2BoltonAre you in need of website services in ? Then contact xyz today on 12345678Are you in need of website services in Bolton ? Then contact xyz today on 12345678
Spare2
Cell Formulas
RangeFormula
C2C2=LEFT(B2,39)&" "&A2&" "&RIGHT(B2,LEN(B2)-39)
 

John Caines

Well-known Member
Joined
Aug 28, 2006
Messages
1,139
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Hi
Try
VBA Code:
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

Well-known Member
Joined
Aug 28, 2006
Messages
1,139
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Aug 28, 2006
Messages
1,139
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,441
Messages
5,624,797
Members
416,054
Latest member
Ariel2219

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top