CODEWITHSUNDEEP
excelexcel-formula
basilbc
basilbc

Reputation: 69

Excel find and replace substring in a string

String is "TABLE1[ROW1]"

Using Find and Replace the substring "TABLE1" should be replaced such that [ROW1] is retained after replacement.

Final String after replacement

INDIRECT(D1&"[ROW1]")

I am having trouble with inserting ") After [ROW1].

I have seen some samples for retaining using "^&", but no solution to this problem.

Kindly Help. Thanks.

Upvotes: 1

Views: 738

Answers (1)

Alexander Bell
Alexander Bell

Reputation: 7918

Having cell A1 containing the string TABLE1[ROW1], using Excel worksheet function SUBSTITUTE as following: =SUBSTITUTE(A1,"TABLE1", ""), then adding the word "INDIRECT(D1&" resulting in the cell A2 formula:

="INDIRECT(D1&" & """" & SUBSTITUTE(A1,"TABLE1", "") & """" & ")",

transforming the original string TABLE1[ROW1] into the string: INDIRECT(D1&"[ROW1]") as per your requirement.

Hope this will help. Rgds

Upvotes: 1

Related Questions