Prev: Combining text from multiple cells
Next: Lose Formatting
From: Homecomingwarrior on 12 Apr 2010 16:08
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

From: Rick Rothstein on 12 Apr 2010 17:09
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?

--
Rick (MVP - Excel)



"Homecomingwarrior" <Homecomingwarrior(a)discussions.microsoft.com> wrote in
message news:6D22B5EC-3861-4C3C-9B08-26A3A9FE18EA(a)microsoft.com...
> 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
>
From: Joe User on 12 Apr 2010 18:10
"Homecomingwarrior" wrote:
> 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 -----

"Homecomingwarrior" wrote:
> 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
>
 | 
Pages: 1
Prev: Combining text from multiple cells
Next: Lose Formatting