Need to extract specific data from a text column

[Homecomingwarrior]

VFLE_VT_1CAAGK_790006_BH4A004M02_STALVTBAKAF


From this string I may need to extract 1CAAGK or 790006 or BH4A004M02

Not all rows of data are consistent in the wording however they will have a
6 digit alfa string starting with a 1; a 6 digit string starting with 7 or 8
and a 10 digit alfa/numeric with no specific beginning number or alfa.

Though about delimiting the entire worksheet but still would require manual
input ro remove unneeded data.

Currently someone plugs the number/digit that we are looking for.

please help.

just beginning...
Thanks !
-W

 

[Rick Rothstein]

I think it would be helpful to us if your provide a little more detail about
the structure of the text. Will the parts you are interested in **always**
be separated by underline characters? If so, will there always be two of
them before the first string you wish to extract? Also, if so, will there
**always** be 5 underline characters (that is, 6 delimited pieces of text)?
Will there ever be any numbers in the text before the first string you wish
to extract? Are the three strings you wish to extract **always** grouped
together (one following the other) as your example shows?

 

[Joe User]

VFLE_VT_1CAAGK_790006_BH4A004M02_STALVTBAKAF

From this string I may need to extract 1CAAGK or 790006
or BH4A004M02

Not all rows of data are consistent in the wording however
they will have a 6 digit alfa string starting with a 1; a 6
digit string starting with 7 or 8 and a 10 digit alfa/numeric
with no specific beginning number or alfa.

It would be helpful to know if the first two parts of the string are always
8 characters, the length of "VFLE_VT_".

If they are, then (if the entire string is in A1) because you wrote: "they
will have a 6 digit alfa string[...]; a 6 digit string [...] and a 10 digit
alfa/numeric"....

To extract third field (e.g. "1CAAGK"):
=mid(A1,9,6)

To extract the fourth field (e.g. "790006"):
=mid(A1,16,6)

To extract the fifth field (e.g. "BH4A004M02")"
=mid(A1,23,10)

If you cannot count on an 8-character prefix, but if the second part (e.g.
"VT") will never start with 1, then (because you wrote: " they will have a 6
digit alfa string starting with a 1"):

=mid(A1,1+SEARCH("_1",A1),6)

=mid(A1,8+SEARCH("_1",A1),6)

=mid(A1,15+SEARCH("_1",A1),10)

If you cannot count on an 8-character prefix, and if the second part (e.g.
"VT") might start with 1, then:

=mid(A1,2+SEARCH("_",SUBSTITUTE(A1,"_","",1)),6)

=mid(A1,9+SEARCH("_",SUBSTITUTE(A1,"_","",1)),6)

=mid(A1,16+SEARCH("_",SUBSTITUTE(A1,"_","",1)),10)


----- original message -----