CODEWITHSUNDEEP
excelexcel-formula
MK01111000
MK01111000

Reputation: 822

How do I substitute text with variable content

With the substitute statement I am able to replace text. I have however a situation in which I don't know how to solve it. Is there a formula-solution to accomplish the following:

currently: A1 | BLABLABLA WITH 15% OR MORE BLABLABLA

expected outcome A1 | BLABLABLA WITH NOT LESS THAN 15% BLABLABLA

Note that the "BLABLABLA" text is variable and so is the percentage.

Upvotes: 0

Views: 70

Answers (1)

Variatus
Variatus

Reputation: 14383

The formila I developed for you has the original text in A2. Please paste it on the same tab where you want the modified string to appear.

=SUBSTITUTE(A2,LEFT(MID(A2,SEARCH("with *% or more",A2)+5,10),FIND("%",MID(A2,SEARCH("with *% or more",A2)+5,10)) -1)&"% or more","at least "&LEFT(MID(A2,SEARCH("with *% or more",A2)+5,10),FIND("%",MID(A2,SEARCH("with *% or more",A2)+5,10)) -1)&"%")

Below is the complete history of development where you can see the steps taken. This will be useful if you ever want to modify it.

enter image description here

Upvotes: 2

Related Questions