Parse text with a keyword "else"

[jrosen12]

Hi,

I am trying to parse a string beginning at the word "else" and ending at a number.

if(LowIncomeSubsidyLevel = 1) then 27.00 else (if(LowIncomeSubsidyLevel=0.75) then 33.40 else (if( LowIncomeSubsidyLevel=0.50) then 39.80 else (if(LowIncomeSubsidyLevel=0.25) then 46.30 else 52.70)))

The results should be parsed as:

else (if(LowIncomeSubsidyLevel=0.75) then 33.40
else (if( LowIncomeSubsidyLevel=0.50) then 39.80
else (if(LowIncomeSubsidyLevel=0.25) then 27.00
else 52.70)))

I have been using the MID formula with somewhat success.

 

[jtakw]

Hi,

You didn't specify whether you want the results in separate Columns or Rows, I've assumed Rows, otherwise, formula will need modification:

Book1
	A
1	if(LowIncomeSubsidyLevel = 1) then 27.00 else (if(LowIncomeSubsidyLevel=0.75) then 33.40 else (if( LowIncomeSubsidyLevel=0.50) then 39.80 else (if(LowIncomeSubsidyLevel=0.25) then 46.30 else 52.70)))
2	else (if(LowIncomeSubsidyLevel=0.75) then 33.40
3	else (if( LowIncomeSubsidyLevel=0.50) then 39.80
4	else (if(LowIncomeSubsidyLevel=0.25) then 46.30
5	else 52.70)))
Sheet341

Cell Formulas
Range	Formula
A2	=TRIM(MID(SUBSTITUTE(A$1," else",REPT(" ",255)&"else"),ROWS(A$2:A2)*255,255))

Formula copied down.