Extract value from a text string

  • Thread starter Thread starter Dinesh
  • Start date Start date
D

Dinesh

Hi,

I have three slightly three different text string where I want to extract a
proceed value (672707.58) from it. Below are the text strings.

1) XYZ Corp MLA 2799 SCH 053-000 ABC-72494 Proceeds to ABC for - 672707.58
2) XYZ Corp MLA 2799 SCH 053-000 ABC-72494 Proceeds to ABC Inc for 672707.58
3) XYZ Corp MLA 2799 SCH 053-000 ABC-72494 Proceeds to ABC Inc 672707.58

Below is a formual that works only on the first scenerio. For the second and
third text string, I get a "#value" error.

=IF(ISERROR(SEARCH("ABC",C19)),"0",MID(C19,SEARCH("- ",C19)+1,99)+0)

Thanks,

Dinesh
 
Try this:
=RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1,"
",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

This assumes your data is in cell A1.

Let me know if it works.
 
Since the number to extract seem to *always* be at the end of the string:

=--TRIM(RIGHT(SUBSTITUTE(C19," ",REPT(" ",255)),255))
 
Thanks. Two things.

The first string has a dash before the amount, and it is not a minus sign.
how can I avoid that?

What if I have an additional text after the proceeds value. ThenI believe
this formula will not work.
 
Hi,

Yes, the amount is always at the end. I have hundreds of text string. So I
want to extract only if the proceeds is related to"ABC". That is one criteria
that I forgot to emphasis.

Thanks,
Dinesh
 
Hi

Then combine your test with Biff's solution
=IF(ISERROR(SEARCH("ABC",C19)),"0",
--TRIM(RIGHT(SUBSTITUTE(C19," ",REPT(" ",255)),255)))
 
Use your originally posted ISERROR test coupled with Biff's formula (replace
the IF function's 2nd argument... the MID function call... with Biff's
formula, minus the equal sign, of course).
 
Ok, just add your error trap to the beginning of the formula:

=IF(ISERROR(SEARCH("ABC",C19)),0,--TRIM(RIGHT(SUBSTITUTE(C19," ",REPT("
",255)),255)))

=IF(COUNT(SEARCH("ABC",C19)),--TRIM(RIGHT(SUBSTITUTE(C19," ",REPT("
",255)),255)),0)

=IF(COUNTIF(C19,"*ABC*"),--TRIM(RIGHT(SUBSTITUTE(C19," ",REPT("
",255)),255)),0)
 
=(COUNTIF(A1,"*ABC*")>0)*TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

No need to test for >0. COUNTIF will return 1 or 0.

1*TRIM(...) = the number
0*TRIM(...) = 0

Assuming the extracted string is always a number:

=COUNTIF(A1,"*ABC*")*TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

You can even remove the TRIM function and it'll work. But, not knowing the
full extent of possible data entries I'd still leave it in the formula.

=COUNTIF(A1,"*ABC*")*RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)
 
Back
Top