extracting data from a string

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

Guest

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
 
R

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))
 
G

Guest

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

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



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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Extracting Data From The Right of a Cell 1
Modifying a complex date formual to count months 4
Rotating Work Schedule 7
Ignoring Rows When Extracting Data From One Worksheet To Another 2
Comparing Cells and Displaying Data 1
Extracting text from right to left using MID and FIND 6
IF and LEN and MID Problem 5
Obtain Month & Year from a Text formatted Date field using Left Fu 2

Top