Extract value from a text string
 

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

Extract value from a text string
 

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

Extract value from a text string
 

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

Extract value from a text string
 

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

Extract value from a text string
 

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

Extract value from a text string
 

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

Extract value from a text string
 

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

Extract value from a text string
 

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

Extract value from a text string
 

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

Extract value from a text string
 

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

Extract value from a text string
 

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

Extract value from a text string
 

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