On Wed, 9 Aug 2006 18:14:02 -0700, Dinesh
wrote:

Hi,

I like to extract a value at the end of text string. The long text string
ends with "Inc. for - 356989.23".

Is there a formula where I can just extract the value of "356989.23"
only.The values can in hundreds, thousands, millions, but there is always a
space between a hyphen and where the value start.

Thanks

Dinesh


The format of the string is important.

Is it always the last number in the string?
Is the number always positive, or might it be negative, also?
Is the number always at the end of the string, or could there be text following
the number?

Will the number always have digits to the left of the decimal?

One way would be to download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/ and use a Regular Expression.

If it is always the last value in the string, and will always have digits to
the left of the decimal,and always a positive number, then:

=REGEX.MID(A1,"\d+(\.\d*)?$")

If there could be non-numeric characters following the number, and if it could
also be negative, and if there might not be any digits to the left of the
decimal, then:

=REGEX.MID(A1,"-?(\d+(\.\d*)?|\.\d+)",-1)

would probably due the trick.

If you need to return the value as a number (and not a text string),then
something like:

=IF(ISNUMBER(-REGEX.MID(A1,"\d+(\.\d*)?$")),--REGEX.MID(A1,"\d+(\.\d*)?$"),"")


--ron