# Replace Text from 2nd Column in between Delimiters in First

#### rjb1

##### Board Regular
Hi,

I have two columns of text.
A B
"ABCD" "CDEF" REPTEXT1
"GHIJ" "KLMN" REPTEXT2

I need a macro to take the replacement text and put it in between
the delimiter of choice. In this case, the second set of quotes.

Result:
"ABCD""REPTEXT1"
"GHIJ""REPTEXT2"

Thanks!

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
If the text is consistent :_
=CONCATENATE(LEFT(A1,6),"""",B1,"""")

Thanks Brian but the text is not consistent.

"Thanks Brian but the text is not consistent."

- will the delimiter always be a "
- will you always be replacing between the third and forth instances of the delimiters (or.for exampole, is it between the last pair?)
- is the text to replace always 4 characters long?

Not the most efficient formula, but I believe this array entered formula will do the trick: (I'm assuming the text in Cell A1 is no more than 100 characters long.)

Code:
``````=REPLACE(A1,SMALL((MID(A1,ROW(1:100),1)<>"""")*9.9E+307+ROW(1:100),3)+1,
SMALL((MID(A1,ROW(1:100),1)<>"""")*9.9E+307+ROW(1:100),4)-
SMALL((MID(A1,ROW(1:100),1)<>"""")*9.9E+307+ROW(1:100),3)-1,"ReplaceText1")``````

Confirmed with Control + Shift + Enter

Replies
5
Views
150
Replies
3
Views
716
Replies
2
Views
376
Replies
2
Views
606
Replies
3
Views
157

1,203,052
Messages
6,053,234
Members
444,648
Latest member
sinkuan85

### 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.

### Which adblocker are you using?

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

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