Extract specific value from a long text string

[Guest]

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

 

[Guest]

One try ..
Assuming data in A1 down
In B1, copied down:
=MID(A1,SEARCH("- ",A1)+1,99)+0

 

[Guest]

Hi Max,

Thanks, It worked. but what if the string does not have the string "- ", it
gave an error of "#value!"?

 

[Guest]

Thanks, It worked. but what if the string does not have the string "- ", it
gave an error of "#value!"?

Then it would fail, but of course. The core assumption made was the
existence of the string "- ", as interp'd from your original posts' line:
Maybe hang around awhile for better insights from others for a generic
solution which could work to strip out the numbers irrespective. Or put in a
new post showing all the different types of sample text strings present, and
the expected results for each.

---

 

[Ron Rosenfeld]

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