Using VBA Replace Function to add to a specific part of a string excel

Question

The output of a script I have is generating a string of two sentences, now separated only with a space. Currently, the script has joined the strings from A1 and A2 to get A3

A1 Mary had

A2 a little lamb

A3 Mary had a little lamb

However, I'd like to insert a carriage return to separate the two strings using the replace function.

It feels like there should be a way to have excel count the string in A1 and then add a carriage return that far into A3.

What I have so far is below, but it's currently replacing all of Mary had with a return. Is there something simple I'm missing?

Range = Replace(A3, Left(A3, InStr(A3.Value, A1.Value)), vbLf)

Much appreciated.

Issam Mani · Accepted Answer · 2019-11-19 17:58:43Z

1

If I understand correctly, what you want to do is concatenate the values of cells A1 and A2, adding a new line between them, and set the result to cell A3. You can do it fairly easy as such :

A3.Value = A1.Value & vbNewLine & A2.Value

answered Nov 19, 2019 at 17:58

Issam Mani

911 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zack E · Accepted Answer · 2019-11-19 17:58:01Z

1

This is based off the guess that A1 and A2 are already designated as strings. I dont think you need a replace function unless its required for something else in your project.

Option Explicit
Sub Test()
    Dim A1 As String, A2 As String, A3 As String

    A1 = "Mary had"
    A2 = "a little lamb"

    A3 = A1 & vbCrLf & A2

    Debug.Print A3

End Sub

answered Nov 19, 2019 at 17:58

Zack E

7182 gold badges7 silver badges24 bronze badges

Collectives™ on Stack Overflow

Using VBA Replace Function to add to a specific part of a string excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related