CODEWITHSUNDEEP

Home
Blog
Discussion

excelexcel-formula

Myslnik

Reputation: 37

How do I write a formula that replaces a text string that contains quote marks?

I have a cell in Excel, which, for the sake of example, contains this:

<text style="bold">Text.</text>
<text style="bold">More text.</text>
<text style="bold"></text>
<text style="bold">Other text.</text>
<text style="bold"></text>

Now, I would like to write a formula that deletes all text style tags that don't contain any text, i.e. all <text style="bold"></text>.

Since the text string contains quotes, this formula doesn't work:

=SUBSTITUTE(a1,"<text style="bold"></text>", "")

I have also tried to work around this by writing this formula

=SUBSTITUTE(a1,CONCATENATE("<text style=", char(34), "bold", char(34), ">","</text>"), "")

but that doesn't seem to work either.

How do I write a formula that replaces a text string that contains quote marks?

Please help.

Upvotes: 0

Views: 842

Answers (3)

Reputation: 352

You can use the ascii code for " to do this.

=SUBSTITUTE(SUBSTITUTE(A1,"<text style="&CHAR(34)&"bold"&CHAR(34)&">",""),".</text>","")

Upvotes: 3

user4039065

Reputation:

How do I write a formula that replaces a text string that contains quote marks?

Oddly enough, Excel has a REPLACE function. It has already be described how you need to double-up quotes within a quoted string so I will simply offer an alternative to SUBSTITUTE putting the doubled-up quotes in place.

=REPLACE(A1, IFERROR(FIND("<text style=""bold""></text>", A1), LEN(A1)+1), LEN("<text style=""bold""></text>"), TEXT(,))

Upvotes: 1

Scott Craner

Reputation: 152525

SUBSTITUTE looks for an exact match somewhere in the text, and all quotes should be doubled.

The text you are using in the formula does not exist exactly in any of the examples given, so I adjusted for that to show proof.

Put double quotes around the "bold":

=SUBSTITUTE(A1,"<text style=""bold""></text>", "")

Upvotes: 3

Related Questions