Find and replace text within a cell and replace with all text from another cell

PeetG · Sep 1, 2021

G'day, I am new to this forum and pretty new to excel especially when it comes to coding so a step by step how to do would be great thanks.

I am trying to find and replace specific text "xxxxxxxxxx" within a cell C2 with all data from another cell B2; this needs to be done for all rows from 2-467

For example in cell C2 is a product description which I have inserted xxxxxxxxxx where I need the value from cell B2 to be inserted. (in place of xxxxxxxxxx)
*C2 is a product description with a email HTML enquiry button embedded, cell B2 is the related SKU, this will insert the SKU into the enquiry email.

A step by step how to do would be great thanks so much! Cheers Pete

products_2021-09-01_13-26_export_after_updated with HTML BO-E_Button.xlsm

A

B

C

1

name

sku

description

2

Battery Yuasa 12v10Ah AGM SLA

BAT011250

Battery - OEM replacement lead acid, 12V 10ah. Deep Cycle Gel Matt Yuasa Battery OEM <ul><li>12Eco / Racing - order 2 pieces 24v </li><li>16Eco / Racing, 20Lite - order 3 pieces 36v</li><li>20Eco / Racing - order 4 pieces 48v</li></ul>Looking for more run time, upgrade to our <a href="https://store60743265.company.site/products/LITHIUM-c111992516" target="_blank"><img src="https://s3.amazonaws.com/images.ecwid.com/images/wysiwyg/product/60743265/364950833/16293460045061364553986/button_lithium_power_paks_jpg" alt="Go to Lithium Power Paks"></a> <a href="mailto:peteg@oset.com.au?CC=info@oset.com.au&Subject=OSETBikesDownUnder%20Backorder%20or%20Parts%20Enquiry&Body=Dear%20OSET%20Bikes%20DownUnder%2C%0AWe%20would%20like%20to%20enquire%20about%3A%0A%0AITEM%3A%20xxxxxxxxxx%0A%0AFor%20our%0AOSET%20Bike%20Model%3A%0AYear%20Model%3A%0A%0AName%3A%0AMobile%3A" target="_blank"><img alt="Back Order Enquiry" src="https://dabuttonfactory.com/button.jpg?t=BACK-ORDER+or+ENQUIRE&f=Open+Sans-Bold&ts=14&tc=fff&hp=30&vp=10&c=11&bgt=unicolored&bgc=5ea83d" border="0" style="float: left; margin: 0px 10px 10px 0px;"></a>

3

Battery OSET OEM Lithium-ion 48v, 20Ah

BAT022825

<div>OSET lithium-ion 48v, 20Ah, black 936 Wh. Black casing, on/off LED button, diagnostic and discharge/charge ports and vent. </div>Celxpert. Inc. UN carton 24.0R Jr 2017A+/ 24.0R 2017A+/ MX-10 2017A+/20R Mk2Li 2019c+<ul> <li>Can be fitted to 20 Eco and Racing from 2014+ with adaptor lead and charger - please request on order comment.</li> </ul><a href="mailto:peteg@oset.com.au?CC=info@oset.com.au&Subject=OSETBikesDownUnder%20Backorder%20or%20Parts%20Enquiry&Body=Dear%20OSET%20Bikes%20DownUnder%2C%0AWe%20would%20like%20to%20enquire%20about%3A%0A%0AITEM%3A%20xxxxxxxxxx%0A%0AFor%20our%0AOSET%20Bike%20Model%3A%0AYear%20Model%3A%0A%0AName%3A%0AMobile%3A" target="_blank"><img alt="Back Order Enquiry" src="https://dabuttonfactory.com/button.jpg?t=BACK-ORDER+or+ENQUIRE&f=Open+Sans-Bold&ts=14&tc=fff&hp=30&vp=10&c=11&bgt=unicolored&bgc=5ea83d" border="0" style="float: left; margin: 0px 10px 10px 0px;"></a>

4

Battery bag (36V) for 16E, 16R, 20L

BAT031453

Battery bag - suitable for 36V, nylon, black, 120 x 160 x 205mm, nylon & velcro. 16.0E/ 16.0R/ 20.0L <a href="mailto:peteg@oset.com.au?CC=info@oset.com.au&Subject=OSETBikesDownUnder%20Backorder%20or%20Parts%20Enquiry&Body=Dear%20OSET%20Bikes%20DownUnder%2C%0AWe%20would%20like%20to%20enquire%20about%3A%0A%0AITEM%3A%20xxxxxxxxxx%0A%0AFor%20our%0AOSET%20Bike%20Model%3A%0AYear%20Model%3A%0A%0AName%3A%0AMobile%3A" target="_blank"><img alt="Back Order Enquiry" src="https://dabuttonfactory.com/button.jpg?t=BACK-ORDER+or+ENQUIRE&f=Open+Sans-Bold&ts=14&tc=fff&hp=30&vp=10&c=11&bgt=unicolored&bgc=5ea83d" style="float: left; margin: 0px 10px 10px 0px;" border="0"></a>

5

Battery bag (48V) for 20E, 20R

BAT031454

Battery bag - suitable for 48V, nylon, black, 120 x 220 x 220mm, nylon & velcro. 20.0E/ 20.0R <a href="mailto:peteg@oset.com.au?CC=info@oset.com.au&Subject=OSETBikesDownUnder%20Backorder%20or%20Parts%20Enquiry&Body=Dear%20OSET%20Bikes%20DownUnder%2C%0AWe%20would%20like%20to%20enquire%20about%3A%0A%0AITEM%3A%20xxxxxxxxxx%0A%0AFor%20our%0AOSET%20Bike%20Model%3A%0AYear%20Model%3A%0A%0AName%3A%0AMobile%3A" target="_blank"><img alt="Back Order Enquiry" src="https://dabuttonfactory.com/button.jpg?t=BACK-ORDER+or+ENQUIRE&f=Open+Sans-Bold&ts=14&tc=fff&hp=30&vp=10&c=11&bgt=unicolored&bgc=5ea83d" style="float: left; margin: 0px 10px 10px 0px;" border="0"></a>

6

Battery parts - discharge port cover, black

BAT042583

Battery parts - discharge port cover, black M24 For 20R24R MX10 <a href="mailto:peteg@oset.com.au?CC=info@oset.com.au&Subject=OSETBikesDownUnder%20Backorder%20or%20Parts%20Enquiry&Body=Dear%20OSET%20Bikes%20DownUnder%2C%0AWe%20would%20like%20to%20enquire%20about%3A%0A%0AITEM%3A%20xxxxxxxxxx%0A%0AFor%20our%0AOSET%20Bike%20Model%3A%0AYear%20Model%3A%0A%0AName%3A%0AMobile%3A" target="_blank"><img alt="Back Order Enquiry" src="https://dabuttonfactory.com/button.jpg?t=BACK-ORDER+or+ENQUIRE&f=Open+Sans-Bold&ts=14&tc=fff&hp=30&vp=10&c=11&bgt=unicolored&bgc=5ea83d" style="float: left; margin: 0px 10px 10px 0px;" border="0"></a>

7

Battery OSET Lithium - charge port cover, silver M17

BAT042584

OSET Lithium - charge port cover, silver M17 knurled 20.0R Mk.2 Li 2019C+/24.0R Jr 2017A-2019C/ 24.0R 2017A+/ MX-10 2017A+ <a href="mailto:peteg@oset.com.au?CC=info@oset.com.au&Subject=OSETBikesDownUnder%20Backorder%20or%20Parts%20Enquiry&Body=Dear%20OSET%20Bikes%20DownUnder%2C%0AWe%20would%20like%20to%20enquire%20about%3A%0A%0AITEM%3A%20xxxxxxxxxx%0A%0AFor%20our%0AOSET%20Bike%20Model%3A%0AYear%20Model%3A%0A%0AName%3A%0AMobile%3A" target="_blank"><img alt="Back Order Enquiry" src="https://dabuttonfactory.com/button.jpg?t=BACK-ORDER+or+ENQUIRE&f=Open+Sans-Bold&ts=14&tc=fff&hp=30&vp=10&c=11&bgt=unicolored&bgc=5ea83d" style="float: left; margin: 0px 10px 10px 0px;" border="0"></a>

8

Battery OSET Lithium - diagnostic port cover, silver M12

BAT042585

OSET Lithium - diagnostic port cover, silver M12 knurled 20.0R Mk.2 Li 2019C+/24.0R Jr 2017A-2019C/ 24.0R 2017A+/ MX-10 2017A+ <a href="mailto:peteg@oset.com.au?CC=info@oset.com.au&Subject=OSETBikesDownUnder%20Backorder%20or%20Parts%20Enquiry&Body=Dear%20OSET%20Bikes%20DownUnder%2C%0AWe%20would%20like%20to%20enquire%20about%3A%0A%0AITEM%3A%20xxxxxxxxxx%0A%0AFor%20our%0AOSET%20Bike%20Model%3A%0AYear%20Model%3A%0A%0AName%3A%0AMobile%3A" target="_blank"><img alt="Back Order Enquiry" src="https://dabuttonfactory.com/button.jpg?t=BACK-ORDER+or+ENQUIRE&f=Open+Sans-Bold&ts=14&tc=fff&hp=30&vp=10&c=11&bgt=unicolored&bgc=5ea83d" style="float: left; margin: 0px 10px 10px 0px;" border="0"></a>

9

Brake Cable Front For 12.5R

BRK011293

Brake cable front For 12.5 Racing, 630*780mm. <a href="mailto:peteg@oset.com.au?CC=info@oset.com.au&Subject=OSETBikesDownUnder%20Backorder%20or%20Parts%20Enquiry&Body=Dear%20OSET%20Bikes%20DownUnder%2C%0AWe%20would%20like%20to%20enquire%20about%3A%0A%0AITEM%3A%20xxxxxxxxxx%0A%0AFor%20our%0AOSET%20Bike%20Model%3A%0AYear%20Model%3A%0A%0AName%3A%0AMobile%3A" target="_blank"><img alt="Back Order Enquiry" src="https://dabuttonfactory.com/button.jpg?t=BACK-ORDER+or+ENQUIRE&f=Open+Sans-Bold&ts=14&tc=fff&hp=30&vp=10&c=11&bgt=unicolored&bgc=5ea83d" style="float: left; margin: 0px 10px 10px 0px;" border="0"></a>

products_2021-09-01_13-26_expor

GWteB · Sep 1, 2021

Hi Pete, welcome to Mr.Excel!

Try this. It ads a new worksheet to your workbook and puts the changed data over there so you're able to see if the result is what you are looking for.

VBA Code:

Public Sub PeetG()

    Const PLACEHOLDER As String = "xxxxxxxxxx"

    Dim arr As Variant, rng As Range, i As Long

    With ThisWorkbook.ActiveSheet
        Set rng = .Range("B2:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).Resize(, 2)
        arr = rng.Value2

        For i = LBound(arr, 1) To UBound(arr, 1)
            arr(i, 2) = VBA.Replace(arr(i, 2), PLACEHOLDER, arr(i, 1))
        Next i

        ' put data on a new sheet for safety reasons
        With .Parent.Worksheets.Add
            .Range("B2").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
        End With

        ' put data where it came from
        ' rng.Value = arr

    End With
End Sub

PeetG · Sep 1, 2021

GWteB said:

Hi Pete, welcome to Mr.Excel!

Try this. It ads a new worksheet to your workbook and puts the changed data over there so you're able to see if the result is what you are looking for.

VBA Code:

Public Sub PeetG()

    Const PLACEHOLDER As String = "xxxxxxxxxx"

    Dim arr As Variant, rng As Range, i As Long

    With ThisWorkbook.ActiveSheet
        Set rng = .Range("B2:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).Resize(, 2)
        arr = rng.Value2

        For i = LBound(arr, 1) To UBound(arr, 1)
            arr(i, 2) = VBA.Replace(arr(i, 2), PLACEHOLDER, arr(i, 1))
        Next i

        ' put data on a new sheet for safety reasons
        With .Parent.Worksheets.Add
            .Range("B2").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
        End With

        ' put data where it came from
        ' rng.Value = arr

    End With
End Sub

G'day GWteB,
That worked perfectly thanks very much, code is bit beyond me as I've never studied like HTML so appreciate you skills thanks!
Take care, cheers PeteG

GWteB · Sep 2, 2021

You're welcome and thanks for letting me know.

Find and replace text within a cell and replace with all text from another cell

PeetG

New Member

Excel Facts

GWteB

Well-known Member

PeetG

New Member

GWteB

Well-known Member

Forum statistics

Share this page

Find and replace text within a cell and replace with all text from another cell

PeetG

New Member

Excel Facts

GWteB

Well-known Member

PeetG

New Member

GWteB

Well-known Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock