Finding a character after two BREAKS

[rlaubert]

I need to find the first instance of a Character > ASCII(64) AFTER <br><br> (<br><br> can be replaced with any uncommon character).
I have a column in Excel that includes ingredients and Instructions without any break codes. I need to separate these two into individual columns.

Sample Cell:
Serves/Yields 6 to 8 appetizer servings <br><br>2 tablespoons firmly packed brown sugar<br>2 tablespoons coarsely chopped pecans or almonds<br>1 1/2 teaspoons brandy, (or other liqueur - I used Kahlua)<br>1 firm-ripe whole brie cheese (8 ounce)<br>Crackers<br>Apple and pear slices <br><br>Stir together the brown sugar, chopped pecans and brandy. <br><br>Place cheese in a baking dish just slightly larger than the width of the cheese. Bake in a 425 degree F oven just until cheese begins to soften in the center, about 6 minutes. <br><br>Evenly sprinkle sugar mixture over cheese. Bake until sugar melts and cheese is melted in center (cut to test), 3 to 5 minutes longer. Scoop hot cheese onto crackers and apple/pear slices to eat.</p>


I need to find the first occurrence of a double break with a LETTER following not a number. In the sample above STIR is the break point between ingredients and instructions.

So I need something like FIND <BR>+<BR>+CHAR(65) OR GREATER

[Pauleyb]

This has been open for a while. Two solutions come to mind.

First, is to use SUBSTITUTE to replace all of the <br># instances with something like 'aa'. It would be important to replace the <br># with something that is the same length (i.e. 2 characters). Wrap all of that around a FIND command to find the <br><br> pair. Tedious, but it should work. As an example, with the original string in A1, I am using 'z' as your <br> character, and I only did the numbers 0 to 1; you would need to expand it to be 0 to 9.
=FIND("zz",SUBSTITUTE(SUBSTITUTE(A1,"z0","aa"),"z1","aa"))+2

Second is to go into VBA and use some regular expressions. If you want to go that route, then I'd recommend you post to the VBA forum.

[Bo_Ry]

Data at A3

Please try at A4
=TRIM(MID(SUBSTITUTE(A3,"<br><br>",REPT(" ",LEN(A3))),MATCH(TRUE,INDEX(CODE(TRIM(MID(SUBSTITUTE(A3,"<br><br>",REPT(" ",LEN(A3))),ROW(A1:A9)*LEN(A3),LEN(A3))))>64,),)*LEN(A3),LEN(A3)))