extracting data from a string

[Iguss]

Dear,

I already have exhausted my sources to find a formula that would extract
specific text based on a criteria.

So here it is

I have

Callable - Sinking Fund 05/01/08@100 - Escrowed

I need to extract text 05/01/08@100 from that string if it is "Callable",
and move remaining text to next column if it isn't.

=IF(ISNUMBER(FIND("Callable",C60)),RIGHT(C60,3+LEN(C60)-FIND("/",C60)),LEFT(C60,LEN(C60)-FIND(" ",C60)))
That takes care of left part and leaved 05/01/08@100 - Escrowed

However this
=IF(ISNUMBER(FIND("Callable",C61)),(LEFT(C61,LEN(C61)-FIND("-",C61))))
gives Callable - Sinking Fund 05/01/08@100

Which part of the function I am missing (MID) that will give me exactly

if Callable then 05/01/08@100 if not then Escrowed goes to next
column.

Thank you for your help.

 

[Max]

One crack at this to try out ..

Source data as posted assumed representative and running in C60 down,
eg: Callable - Sinking Fund 05/01/08@100 - Escrowed

Put in D60:
=IF(ISNUMBER(SEARCH("Callable",C60)),TRIM(MID(C60,SEARCH("
",C60,SEARCH("@",C60)-9)+1,SEARCH(" ",C60,SEARCH("@",C60)+1)-SEARCH("
",C60,SEARCH("@",C60)-9))),"")

D60 returns: 05/01/08@100

Put in E60:
=IF(ISNUMBER(SEARCH("Callable",C60)),"",TRIM(MID(C60,SEARCH("-",C60,SEARCH("
",C60,SEARCH("@",C60)+1)+1)+1,99)))

E60 returns: "" (ie blank)
[If "Callable" is not found in C60's text string, then E60 will return:
Escrowed]

Select D60:E60, copy down as far as required

 

[Roger Govier]

Hi

One way
=IF(LEFT(A1,8)<>"Callable","",
LEFT(MID(A1,FIND("/",A1)-2,255),FIND("-",MID(A1,FIND("/",A1)-2,255))-2))

 

[Iguss]

Great, thank you both

How do I eliminate #Value Error from this

=IF(LEFT(A1,8)<>"Callable","",RIGHT(MID(A1,FIND("@",A1)+7,255),FIND("@",MID(A1,FIND(" ",A1),255))))

Callable - Sinking Fund 12/15/07@101 - Bank Qualified
12/15/07@101 Bank Qualified
Callable 12/15/07@99
12/15/07@99 #VALUE!
Callable - Sinking Fund 05/01/08@100 - Escrowed
05/01/08@100 Escrowed
Callable 05/15/08@99
05/15/08@99 #VALUE!

Thank you again

One crack at this to try out ..

Source data as posted assumed representative and running in C60 down,
eg: Callable - Sinking Fund 05/01/08@100 - Escrowed

Put in D60:
=IF(ISNUMBER(SEARCH("Callable",C60)),TRIM(MID(C60,SEARCH("
",C60,SEARCH("@",C60)-9)+1,SEARCH(" ",C60,SEARCH("@",C60)+1)-SEARCH("
",C60,SEARCH("@",C60)-9))),"")

D60 returns: 05/01/08@100

Put in E60:
=IF(ISNUMBER(SEARCH("Callable",C60)),"",TRIM(MID(C60,SEARCH("-",C60,SEARCH("
",C60,SEARCH("@",C60)+1)+1)+1,99)))

E60 returns: "" (ie blank)
[If "Callable" is not found in C60's text string, then E60 will return:
Escrowed]

Select D60:E60, copy down as far as required

Dear,

I already have exhausted my sources to find a formula that would extract
specific text based on a criteria.

So here it is

I have

Callable - Sinking Fund 05/01/08@100 - Escrowed

I need to extract text 05/01/08@100 from that string if it is "Callable",
and move remaining text to next column if it isn't.

=IF(ISNUMBER(FIND("Callable",C60)),RIGHT(C60,3+LEN(C60)-FIND("/",C60)),LEFT(C60,LEN(C60)-FIND(" ",C60)))
That takes care of left part and leaved 05/01/08@100 - Escrowed

However this
=IF(ISNUMBER(FIND("Callable",C61)),(LEFT(C61,LEN(C61)-FIND("-",C61))))
gives Callable - Sinking Fund 05/01/08@100

Which part of the function I am missing (MID) that will give me exactly

if Callable then 05/01/08@100 if not then Escrowed goes to next
column.

Thank you for your help.

 

[Roger Govier]

Hi

The formula you posted does not produce the results you show.

I can see that the formula I posted does return a #VALUE error, if there is
nothing after the email value.
Use this modification
=IF(LEFT(A1,8)<>"Callable","",IF(LEN(A1)=LEN(SUBSTITUTE(A1,"-",""))+2
,LEFT(MID(A1,FIND("/",A1)-2,255),FIND("-",MID(A1,FIND("/",A1)-2,255))-2),
MID(A1,FIND("/",A1)-2,255)))

--
Regards
Roger Govier

Great, thank you both

How do I eliminate #Value Error from this

=IF(LEFT(A1,8)<>"Callable","",RIGHT(MID(A1,FIND("@",A1)+7,255),FIND("@",MID(A1,FIND("
",A1),255))))

Callable - Sinking Fund 12/15/07@101 - Bank Qualified
12/15/07@101 Bank Qualified
Callable 12/15/07@99
12/15/07@99 #VALUE!
Callable - Sinking Fund 05/01/08@100 - Escrowed
05/01/08@100 Escrowed
Callable 05/15/08@99
05/15/08@99 #VALUE!

Thank you again

One crack at this to try out ..

Source data as posted assumed representative and running in C60 down,
eg: Callable - Sinking Fund 05/01/08@100 - Escrowed

Put in D60:
=IF(ISNUMBER(SEARCH("Callable",C60)),TRIM(MID(C60,SEARCH("
",C60,SEARCH("@",C60)-9)+1,SEARCH(" ",C60,SEARCH("@",C60)+1)-SEARCH("
",C60,SEARCH("@",C60)-9))),"")

D60 returns: 05/01/08@100

Put in E60:
=IF(ISNUMBER(SEARCH("Callable",C60)),"",TRIM(MID(C60,SEARCH("-",C60,SEARCH("
",C60,SEARCH("@",C60)+1)+1)+1,99)))

E60 returns: "" (ie blank)
[If "Callable" is not found in C60's text string, then E60 will return:
Escrowed]

Select D60:E60, copy down as far as required

Dear,

I already have exhausted my sources to find a formula that would
extract
specific text based on a criteria.

So here it is

I have

Callable - Sinking Fund 05/01/08@100 - Escrowed

I need to extract text 05/01/08@100 from that string if it is
"Callable",
and move remaining text to next column if it isn't.

=IF(ISNUMBER(FIND("Callable",C60)),RIGHT(C60,3+LEN(C60)-FIND("/",C60)),LEFT(C60,LEN(C60)-FIND("
",C60)))
That takes care of left part and leaved 05/01/08@100 - Escrowed

However this
=IF(ISNUMBER(FIND("Callable",C61)),(LEFT(C61,LEN(C61)-FIND("-",C61))))
gives Callable - Sinking Fund 05/01/08@100

Which part of the function I am missing (MID) that will give me
exactly

if Callable then 05/01/08@100 if not then Escrowed goes to next
column.

Thank you for your help.