Search a string
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! |
Search a string
Start from the right, not the left:
=RIGHT(A1,4) will get you the four character date field -- Gary''s Student "Arne Hegefors" wrote: 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! |
Search a string
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: Start from the right, not the left: =RIGHT(A1,4) will get you the four character date field -- Gary''s Student "Arne Hegefors" wrote: 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! |
Search a string
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 Arne Hegefors wrote: 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! |
Search a string
You are welcome.
If you are in VBA, then you can use the Split Function. It behaves like Text to Columns in the worksheet. -- Gary''s Student "Arne Hegefors" wrote: 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: Start from the right, not the left: =RIGHT(A1,4) will get you the four character date field -- Gary''s Student "Arne Hegefors" wrote: 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! |
All times are GMT +1. The time now is 05:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com