Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Need to extract specific data from a text column

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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default Need to extract specific data from a text column

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" wrote in
message ...
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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 905
Default Need to extract specific data from a text column

"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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to extract specific text from a string of characters rushdhih Excel Worksheet Functions 7 February 19th 09 10:58 AM
Extract specific text NunRacer Excel Worksheet Functions 15 July 30th 07 01:52 AM
Extract specific value from a long text string Dinesh Excel Worksheet Functions 4 August 11th 06 04:24 AM
Extract Specific Text mjmoore Excel Worksheet Functions 2 June 2nd 06 08:28 PM
extract specific info from cells in a column Herman New Users to Excel 1 October 27th 05 03:01 AM


All times are GMT +1. The time now is 03:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"