Thread: How to remove the content of one cell and replace it with the content of another

Hey all,
In this formula:

=IF(REGEX.COMP(C1,"CANADA"),B1&" "&C1,IF(REGEX.COMP(A1,"(\sTR\s)|(ATTN)"),SUBSTITUTE(A1,A1,B1),""))

what happens is I'm trying to replace this:

SUBSTITUTE(A1,A1,B1)

with an excel formula that won't substitute the content in the column which this function is called, but will rather replace all the content of cell A1 with all the content of cell B1 leaving B1 completely empty.

I tried the REPLACE but it only gave me some kind of #REF value.

Thanks for response.

This not possible to do with formulas? Will I be required to use VBA?

So I made this macro myself:

Sub SearchAndReplace()

Dim RENums As Object
Dim RENums2 As Object
Dim LValue  As String
Dim LR As Long



Set RENums = CreateObject("VBScript.RegExp")
Set RENums2 = CreateObject("VBScript.RegExp")


RENums.Pattern = "CANADA"
RENums2.Pattern = "(\sTR\s)|(ATTN)"

LR = Range("A" & Rows.Count).End(xlUp).Row

    If RENums.Test(Range("C1").Value) Then
    
        LValue = Range("B1") & " " & Range("C1")
    
        Range("B1").Select
        Selection.Clear
        Range("C1").Value = LValue
        
    ElseIf RENums2.Test(Range("A1").Value) Then
    
        Range("B1").Select
        Selection.Cut
        Range("A1").Select
        ActiveSheet.Paste
    
    End If

End Sub

It seems to work, but obviously it is only targeting the first cell. Can anyone help with how I can get it to iterate through column A as long as a cell in Column A has content?

Thanks for response.

I was hoping that this part:

LR = Range("A" & Rows.Count).End(xlUp).Row


would make it loop through the length of the contents of column A but it's not working.

Thanks for response.