Function or VB needed -- Performing numeric operations on a small portion of a block of text.

[kwondra34]

Hi everyone,

I appreciate your help in advance!

I have a string block similar to what is seen below that I export out of a tool (that I cannot edit) -- it looks similar to this:

Matches:

Food,Pizza=Cheese,Topping=Mushroom,Extras=Soda(12),Breadsticks=TooFat
Sports,Baseball=Fun,Basketball=Soso,Soccer=Footy(1500),Badminton=lolseriously


I am trying to write a formula (preferably) or use a VB module to read a string of text and perform a numeric comparison to the portion in paranthesis. So far I have this - which is probably entirely wrong .

 [B]=ISERR(SEARCH("Topping=Mushroom,Extras=Soda( < 12  & ")",B1))[/B]

In the example above, I am trying to do the following -- search for the string "Topping=Mushroom,Extras=Soda(" and then perform a numeric comparison of the following number (12 in the example above). I basically want to match the string and then identify if the number is less than 12.

I would prefer the formula to return the actual finding -- examples being: Extras=Soda(8), Extras=Soda(4), etc., but true/false is also acceptable!



Thank you so very much in advance.

 

[tusharm]

Suppose your string is in B2.

In some cell, say B4 enter the formula =SEARCH("Topping=Mushroom,Extras=Soda(",B2)+LEN("Topping=Mushroom,Extras=Soda(")

This returns the starting position of the number.

In another cell, say C4, enter the formula =SEARCH("),Breadsticks",B2). This gives the starting position of the close paren after the number.

In another cell, say D4, enter =--MID(B2,B4,C4-B4)

That's the number you want.

You could put everything into a single formula but it will be fairly messy. The above is easier to read, understand, and maintain.

Hi everyone,

I appreciate your help in advance!

I have a string block similar to what is seen below that I export out of a tool (that I cannot edit) -- it looks similar to this:

Matches:

Food,Pizza=Cheese,Topping=Mushroom,Extras=Soda(12),Breadsticks=TooFat
Sports,Baseball=Fun,Basketball=Soso,Soccer=Footy(1500),Badminton=lolseriously


I am trying to write a formula (preferably) or use a VB module to read a string of text and perform a numeric comparison to the portion in paranthesis. So far I have this - which is probably entirely wrong .

 [B]=ISERR(SEARCH("Topping=Mushroom,Extras=Soda( < 12  & ")",B1))[/B]

In the example above, I am trying to do the following -- search for the string "Topping=Mushroom,Extras=Soda(" and then perform a numeric comparison of the following number (12 in the example above). I basically want to match the string and then identify if the number is less than 12.

I would prefer the formula to return the actual finding -- examples being: Extras=Soda(8), Extras=Soda(4), etc., but true/false is also acceptable!



Thank you so very much in advance.