• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Replace text between two text references within existing string

S

sabw

New Member
Can someone provide a formula to produce the Desired text string below.

Cell A1: XYZ546

Cell A2: many characters=&symbol=ABC1234&format=tabs

1st reference: symbol=

2nd reference: &format

Cell A3 Desired text string: symbol=XYZ546&format

The text between symbol and &format varies in length.

Thanks
 
Hi, sabw!


Try this:

=EXTRAE(A2;HALLAR("&";A2)+1;HALLAR("=";A2;HALLAR("=";A2)+1)-HALLAR("&";A2))&A1&EXTRAE(A2;HALLAR("&";A2;HALLAR("&";A2)+1);HALLAR("=";A2;HALLAR("=";A2;HALLAR("=";A2)+1)+1)-(HALLAR("&";A2;HALLAR("&";A2)+1))) -----> in english: =MID(A2,SEARCH("&",A2)+1,SEARCH("=",A2,SEARCH("=",A2)+1)-SEARCH("&",A2))&A1&MID(A2,SEARCH("&",A2,SEARCH("&",A2)+1),SEARCH("=",A2,SEARCH("=",A2,SEARCH("=",A2)+1)+1)-(SEARCH("&",A2,SEARCH("&",A2)+1)))


Assuming that "symbol" and "format" might be any other characters than those strings, otherwise you could shorten a little the formula.


Just advise if any issue.


Regards!
 
this was in the middle of his orignial post:


"-----> in english: =MID(A2,SEARCH("&",A2)+1,SEARCH("=",A2,SEARCH("=",A2)+1)-SEARCH("&",A2))&A1&MID(A2,SEARCH("&",A2,SEARCH("&",A2)+1),SEARCH("=",A2,SEARCH("=",A2,SEARCH("=",A2)+1)+1)-(SEARCH("&",A2,SEARCH("&",A2)+1)))"
 
Hi, sabw!

Glad you solved it. Thanks for your feedback and nd welcome back whenever needed or wanted.

Regards!


@Goel

Hi!

Just advise if any issue.

Regards!


@jason

Hi!

Thank you :)

Regards!
 
sub splitNinsert

dim theArray as variant

dim newstring as string


theArray = split(Range("A2").value,"&")

newstring = thearray(0) & "&" & range("A1").value & "&" & theArray(2)

range("A2").value = newstring
 
You must log in or register to reply here.
Back
Top