Extract specific string

  • Thread starter Thread starter Dante Huapaya
  • Start date Start date
D

Dante Huapaya

Hi all,
I need extract a specific string and my cells may contains many alphanumeric
data
In this example I need Extract only S00059997 and S00075671 the LEN is 9 all
the time.
Thanks for your Help

Data_Account
----------------
ANY-NOTES
ANY---NAMES
S00059997
ANY-NOTES
S00075671

Dante
 
I think you may have to tell us more about what you are trying to do and
what you have to work with. First, I'm assuming each item in the list you
showed is on a separate row. Do the strings you are trying to extract always
start with a single letter? If yes, I'm guessing it is not always an "S", is
it? Again, if so, then will only digits follow it? In other words, are you
looking for a single letter followed by eight digits? After you answer the
above questions, here is the most important question... where did you want
these values "extracted" to? An array in VB code? Another column? Somewhere
else?
 
The string start always with "S" and finished with 8 digits and a VB function
will be help me so much.
Thanks

Dante
 
Unfortunately, you didn't answer all the questions I asked, so I am still
not sure how to reply to you. Are the items you showed in your list in a
column, one item per row? Where do you want the extracted strings to go...
an array inside of VB code, another column or somewhere else?
 
- Are the items you showed in your list in a
column, one item per row?
Yes
- Where do you want the extracted strings to go...
an array inside of VB code, another column or somewhere else?
Basically I need a VB function, for example:
if I have in colunmn A a row S00256325 with the function extract this in
column B
any other alphanumeric data column B is blank
ColumnA ColumnB I see
S00256325 =Myextractfunction(A2) S00256325
AnyotherData =Myextractfunction(A2) Blank

Thanks so much for your time!

Dante
 
Assuming your data starts in A2 (with A1 being a header), put this formula
in B2 and copy it down as far as you need to...

=IF(AND(LEN(A2)=9,LEFT(A2)="S",ISNUMBER(--MID(A2,2,8))),A2,"")
 
Thanks for your help, very appreciated!

Rick Rothstein said:
Assuming your data starts in A2 (with A1 being a header), put this formula
in B2 and copy it down as far as you need to...

=IF(AND(LEN(A2)=9,LEFT(A2)="S",ISNUMBER(--MID(A2,2,8))),A2,"")

--
Rick (MVP - Excel)




.
Click to expand...
 
Back
Top