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.


--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



"Dinesh" wrote:

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

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.

"PJFry" wrote:

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.


--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



"Dinesh" wrote:

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

Since the number to extract seem to *always* be at the end of the string:

=--TRIM(RIGHT(SUBSTITUTE(C19," ",REPT(" ",255)),255))

--
Biff
Microsoft Excel MVP


"Dinesh" wrote in message
...
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

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

"T. Valko" wrote:

Since the number to extract seem to *always* be at the end of the string:

=--TRIM(RIGHT(SUBSTITUTE(C19," ",REPT(" ",255)),255))

--
Biff
Microsoft Excel MVP


"Dinesh" wrote in message
...
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

Hi

Then combine your test with Biff's solution
=IF(ISERROR(SEARCH("ABC",C19)),"0",
--TRIM(RIGHT(SUBSTITUTE(C19," ",REPT(" ",255)),255)))

--
Regards
Roger Govier

"Dinesh" wrote in message
...
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

"T. Valko" wrote:

Since the number to extract seem to *always* be at the end of the string:

=--TRIM(RIGHT(SUBSTITUTE(C19," ",REPT(" ",255)),255))

--
Biff
Microsoft Excel MVP


"Dinesh" wrote in message
...
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

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

--
Rick (MVP - Excel)


"Dinesh" wrote in message
...
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

"T. Valko" wrote:

Since the number to extract seem to *always* be at the end of the string:

=--TRIM(RIGHT(SUBSTITUTE(C19," ",REPT(" ",255)),255))

--
Biff
Microsoft Excel MVP


"Dinesh" wrote in message
...
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

Thanks a bunch. After I post it, I figured that one out of a combination.
Appreciated.

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Dinesh" wrote in message
...
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

"T. Valko" wrote:

Since the number to extract seem to *always* be at the end of the string:

=--TRIM(RIGHT(SUBSTITUTE(C19," ",REPT(" ",255)),255))

--
Biff
Microsoft Excel MVP


"Dinesh" wrote in message
...
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

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)

--
Biff
Microsoft Excel MVP


"Dinesh" wrote in message
...
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

"T. Valko" wrote:

Since the number to extract seem to *always* be at the end of the string:

=--TRIM(RIGHT(SUBSTITUTE(C19," ",REPT(" ",255)),255))

--
Biff
Microsoft Excel MVP


"Dinesh" wrote in message
...
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

Hi,

Try this.

=1*MID(B4,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B4&"012 3456789",SEARCH("ABC
",B4,1))),50)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Dinesh" wrote in message
...
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:

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


"Dinesh" wrote:

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

=(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)


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
Try this:

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


"Dinesh" wrote:

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