Sub on varied length string

I have number and letter combinations that create one field, some with dashes, some without and with varied lengths. I need to parse sections before, between, and after the dashes from an Excel import. Ex. one is of the pattern X-XXXX-XX or XX-XXXXX-X, another is XXXXXX, and another is XXXXXX-XXX.

I tried a substring function : left([part],2) but this only works for a few of the parts since the lengths vary. I'm looking for a macro that would separate with "if" loops for the different kinds. There is a small, finite number (say 6 or so).

Any suggestions? The only other way to do it is reconstruct the database so the whole is one field instead of four; but it's not the best solution as the middle (or base) is a number to search.

Thank you.

there are plenty of ways to do this, but I think it depends on how you want the end result displayed.

How do you want it...??? Where are the parsed result gonna be stored? A table? a form? report? how are you going to be searching on it? Just by one field that contains a parsed section of the source data?

<edit>

I have no idea if this is what you want, but it might be close...

Splitting uneven values into separate fields

ajetrumpet said:

there are plenty of ways to do this, but I think it depends on how you want the end result displayed.

How do you want it...??? Where are the parsed result gonna be stored? A table? a form? report? how are you going to be searching on it? Just by one field that contains a parsed section of the source data?

<edit>

I have no idea if this is what you want, but it might be close...

Click to expand...

Adam,

This is exactly what I was thinking. Yes, each of the parsed values must be in separate fields, and I think a form with a table base is where we'll store the data. For right now, I have a table. I have created a query to reestablish the numbers into one whole when people read it. However, for input, it's more efficient to have each part in a different field.

I don't know VB, so what you have created is spectacular! I will input some values I have to see if it works with my record set. I know I'll need to change field values, and I hope I can do this without destroying the program. I'll keep a copy of your work in case I goof it up.

I so appreciate your help. I could not have designed this myself, so you've given me a very big gift. Thank you. I'll return after testing and respond again.

Again, thank you.

Varied length strings to parse

Adam,

Thank you for all your work on this. It is exactly what I was thinking. I sent a message a little earlier today, but I don't think I posted it correctly, so it's at the bottom of the thread.

Your module is wonderful! You did an amazing job of capturing my explanation and making it work! I don't know VB, so I could never have done this myself. This is very close to what I will need, but would you mind helping me tweek it so I don't mess it up?

If a value has one or no hyphens, the beginning value goes in the second field, which I'm calling the base. The fields are prefix (the first number or value before the first hyphen); the base (if the value has one or no hyphens); the "dash" (the values after the second hyphen); and the iteration (a final letter after a number, but only for the letters "T," "R," or "M"). Any other number or number/letter combination after the second hyphen should stay in the "dash" column.

1-1111-1T. Where the final 1 and T would be separated into different fields.
1-1111-1X. Where the final 1X would go into the "dash" column together.
XX111111-11X Where 11X is the "dash."
111AA1-AA. Where AA would be the "dash."
1-11111-111. The last part is the "dash."

I hope this isn't a lot more complicated than what you already have. You can see why someone with no VB would be struggling with this.

Thank you,
Cindy