This will get you a string that starts with the guarantor or the price, where
there is no guarantor

=RIGHT(A1,LEN(A1)-5-SEARCH("\",SUBSTITUTE(A1,"/","\",4)))

More to follow, based on your response to the earlier question


"IPerlovsky" wrote:

You are correct. I am looking for spread. The difference between what you
call YTM and YTC, which are actually the benchmark yield and the bond's yield.

--
iperlovsky


"Duke Carey" wrote:

OK, these lines represent bond issues, right?
Is it fair to say that these would parse into (rough guess)

Date (purchase?)
State of Obligation
CUSIP
Description
Par
Coupon
Maturity
Guarantor
Price
YTM
YTC
?
Call Date

And you want the ? value

What does it represent, and is there a better way of grabbing it?
"IPerlovsky" wrote:

It would appear that way from my examples, but the answer would have to be no.

thanks for giving this another crack...

--
iperlovsky


"Duke Carey" wrote:

So - is it always followed by the "Aaa AAA" sequence?

"IPerlovsky" wrote:

Okay, let me make this a bit clearer: The number I am looking for ("8" in
the example) can be more than one digit (up to 3), but it will always be a
number without decimals and a space on either side. The number in the
example that comes first, which I want to avoid, ("5") can be a number with
up to 2 decimal places - or no decimals like in the example. The formula
should not pull any single letters that have spaces on either side. I
provided 3 other strings as an example:

1/4/2007 CA 544646-BH LOS ANGELES CALIF UN 1000000 5 7/1/2022 FGIC 108.151
3.96 3.86 10 Aaa AAA Y 7/1/2016

...here I am looking for "10"

1/8/2007 CA 544644-NC LOS ANGELES CALIF UN 1000000 5.75 7/1/2016 MBIA
115.907 3.74 3.67 7 Aaa AAA N 7/1/2016

...here for "7"

1/8/2007 CA 544644-NC LOS ANGELES CALIF UN 1000000 5.75 7/1/2016 MBIA
115.907 3.74 3.67 17 Aaa AAA N 7/1/2016

...and here for "17"

your help is greatly appreciated.

--
iperlovsky


"Elkar" wrote:

Hmm... if the number could contain decimal places, then based on your
example, 3.90 would be returned (or perhaps even 108.579 if the number can be
greater than 10). I think we would need to find a better set of criteria
than "a number surrounded by spaces". Are there any other commonalities
amongst your data that we can work with? (ie.. total number of spaces, first
number from the right, etc...)

Or, perhaps if you posted some more examples of your data, we might be able
to see a useful pattern.


"IPerlovsky" wrote:

Thanks, that worked great! One caveat though - what if the number in question
comes with or without a "-" (negative sign) accompaniment (ie, "-8") and/or
the number that I am trying not to extract has a decimal behind it with up to
2 decimpal places (ie, "5.75")?

--
iperlovsky


"Elkar" wrote:

Based solely on the criteria you provided, this should work:

=MID(MID(A1,SEARCH(" ? ",A1)+1,LEN(A1)),SEARCH(" ? ",MID(A1,SEARCH(" ?
",A1)+1,LEN(A1)))+1,1)

HTH,
Elkar


"IPerlovsky" wrote:

How would I extract a number (or series of numbers) using a single cell
formula from an alphanumeric text string that also contains spaces? One key
component of the number(s) in question, is that they have a space on either
side. See the example of the text string below:

"A 544646-BA CALIF UN1MM+ 5 7/1/2017 FSA 108.579 3.90 3.82 8 Aaa AAA"

In this example, I am trying to extract the number "8". However, there is
also the number "5", which appears first, and that I want to avoid
extracting. Additionally, the formula should be able to extract any number,
not specific to "8" or "5". This formula should always skip the first single
number surrounded by spaces ("5" in this example) and extract the second
number(s) ("8" in this example).

--
iperlovsky