Search a string

[Arne Hegefors]

Hello! I try to search a string for a certain combination of charachters. I
have a column that contains the ID for different bonds. The ID contains the
same info for all types of bond but with a some differences. Let me try to
explain the syntax for the ID by giving some examples:
ACGB 7.5 0705
T 4.25 0813
CAN 5.75 0629
CAN 2.75 1207

As you might see the sytax is like this: Name Yield Maturity. These three
fields are ALWAYS separated by "Space". However as you can see the name and
the yield can have a varying number of charachters. I want to single out the
maturity (Always four digits, MMYY). My code now is:
strMaturityDate = Mid(strSecID, 5, 2) but that does not work at all. Please
help me! Thanks very much!

 

[Gary''s Student]

Start from the right, not the left:

=RIGHT(A1,4) will get you the four character date field

 

[Arne Hegefors]

Thanks! However I juts realized that sometimes the maturity is followed by a
letter e.g. X without blanks bewteen. Thus I must search for maturity by
lokking for blanks since it is quite possible that other texts in this filed
may be a four letter combination, e.g. the name ACGB or the yield 5.75 are
four charachter combinations. Is there no way of looking for blanks instead?
Thanks for your help!

"Gary''s Student" skrev:

 

[paul.robinson]

Hi
Have you tried Data, Text to Columns... with "space" as deliminator?
This will split each entry into three, with the third column the bit
you want.
regards
Paul

 

[Gary''s Student]

You are welcome.

If you are in VBA, then you can use the Split Function. It behaves like
Text to Columns in the worksheet.