Excel find and replace substring in a string

Question

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.

Could you please include a formula that you use? Is adding ")" to the end only problem that you have: btw, it should be done simply by & ")". Rgds, — Alexander Bell
– Alexander Bell, Commented Jun 29, 2014 at 13:27
=MIN(IF(TABLE1[ROW1])=S1,TABLE1[ROW2])) To =MIN(IF(INDIRECT(D96&"[ROW1]")=S1,INDIRECT(D96&"[ROW2]"))) D96 is the cell with value TABLE1 — basilbc
– basilbc, Commented Jun 29, 2014 at 13:34

Alexander Bell · Accepted Answer · 2014-06-29 13:54:13Z

1

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

edited Jun 29, 2014 at 13:54

answered Jun 29, 2014 at 13:48

Alexander Bell

7,9263 gold badges29 silver badges44 bronze badges

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

Collectives™ on Stack Overflow

Excel find and replace substring in a string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related