Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wildcard search for string within a string? | Excel Programming | |||
to search for a string and affect data if it finds the string? | Excel Worksheet Functions | |||
search a string withing a string : find / search hangs | Excel Programming | |||
VBA function : How to search a string in another string? | Excel Programming | |||
Excel XP VBA code to search all macro code in Excel module for specific search string criteria | Excel Programming |