Best way to parse array of strings with format A_B_C_Otherstuff

Hi,

I have an array of strings such as this

A_B_C_Otherstuff

The Otherstuff can contain additional underscores and anyother characters. What i want to do is parse the

A_B_C from the Otherstuff



I want to parse the A_B_C into an array and then the Otherstuff into another array.

I am not sure the best way to do this.

Any ideas? Even if all you do is show me the best way to parse the string into two that would be helpful.

sure let me create one

i am enclosing just a test file of what an example string may look like and what the two resultant parsed strigns would look like.

Again, i would like to do this via VBA.

duh......forgot to attach the actual file........

fortis1191, if its not in vba thats ok .....maybe i can convert it myself.........

nilem,

Thanks for the quick response.

Two quick questions:
1) You commented out 3 lines of code.....was the commented out lines "another way to do something"
2) can you explain this line of code...

Please Login or Register  to view this content.

x(i, 2) = Split(x(i, 1), "_", 4)(3)

Suppose x(i, 2) is 'A2_B2_C2_DET2-Rev2_Site.xlsx'
Split(x(i, 1), "_", 4) is an array of four elements: A2 (#0), B2 (#1), C2 (#2), and DET2-Rev2_Site.xlsx (#3)
Split(x(i, 1), "_", 4)(3) is the third element of the array, i.e. DET2-Rev2_Site.xlsx

Try commenting out the line

Please Login or Register  to view this content.

and uncomment the line

Please Login or Register  to view this content.

to get DET2-Rev2_Site instead DET2-Rev2_Site.xlsx

thanks Nilem! That helped a lot!

when i run the code and i use this as a test file name i get a run time error

"A1_B1C1DET1-Rev1Site.xlsx"


Any ideas?

here's an idea

Please Login or Register  to view this content.

thanks Nilem,

I think i understand now why the runtime error occurred. There was a string which did not have 3 "_" characters. So, i think regardless of what i use, if i am going to check the string to make sure it is of the appropriate format then i am going to have to use a different approach than using the split function.

thanks again for your help!!!!

the string format i am trying to check and then parse is of this format.

x_y_z_otherstuff

x => can be any combination of characters other than "_"
y => can be any combination of characters other than "_"
z => can be any combination of characters other than "_"

otherstuff=> can be any combination of characters INCLUDING a "_"

if the string has this format then i can parse it. otherwise i have to let the user know the format is wrong.

nilem,

i used your code plus a little of my own.....may not be the best way but it seems to work.

It also forced me to really think about what is an acceptable string format.

x_y_z_DET_otherstuff2

x => can be any combination of characters other than "_"
y => can be any combination of characters other than "_"
z => can be any combination of characters other than "_"
DET=> string must contain these text characters
otherstuff2=> can be any combination of characters but does NOT include "_"
overall string must contain 4 underscores

if the string has this format then i can parse it and i use the parse code you provided.

i am enclosing a file with this code.....

thanks again nilem!!!!!!!