extracting data from a string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
Hi

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




Max said:
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

---
Iguss said:
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.
Click to expand...
Click to expand...
 
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



Iguss said:
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




Max said:
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

---
Iguss said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top