• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract text from string

S

ssuresh98

Member
Hello,

Can someone help me extract the Batch # from the following string?


"A549_CL130497_SAM602138_Batch100_Plate_5_H4" Answer: 100


The formula that I use, "=(MID($A1,FIND("Batch",$A1)+5,4)*1)". The problem with this formula is that the batch numbers range from 1-1000 and I would like to get exact number without the "_"after the number. My problem is that I do not know how to set the length.


Thanks in advance.
 
Hi Suresh ,


Try this :


=MID(A1,SEARCH("Batch",A1)+LEN("Batch"),SEARCH("_",A1,SEARCH("Batch",A1)+LEN("Batch"))-SEARCH("Batch",A1)-LEN("Batch"))


A1 contains your text string.


Please note that the result of the above formula will also be a text string ; if you want a numeric value , you will have to use the VALUE function outside the above formula.


Narayan
 
or,

Code: 
=MID(A1,FIND("Batch",A1)+5,FIND("_",A1,FIND("Batch",A1))-FIND("Batch",A1)-5)


or the shorter

=MID(A1,FIND("Batch",A1)+5,FIND("_Plate",A1)-FIND("Batch",A1)-5)
 
Thanks to both Narayan and Hui, all 3 formulas work. I prefer to go with Hui's second formula because it is simple, straight forward and I understand MID and FIND function. I am yet to learn the SEARCH and LEN function. Though I have to admit, I don't really understand Hui's first formula. I get lost in the finding the number of characters. Hui can you explain?


In any case it is amazing the kind of talent which frequent this forum. Thanks.
 
Ssuresh98


They are all doing exactly the same thing and just use different assumptions


Narrayan's formula calculates the length of Batch, where I know it is 5 characters


Narrayan's formula uses Search I use Find

Search is Case Insensitive, Find is Case Sensitive

Search allows use of Wild Card characters, Find cannot use wild cards


My second formula assumes there will be a text string _Plate directly after the Batch string


The Len() function returns the length of a text string, I know Batch is 5 characters and so saved text by using 5 instead of Len("Batch")
 
So many ways to do the same thing. That makes excel so versatile.

Thanks Hui for the explanation. I am an excel novice, I think it makes sense now but I have to dissect it further to really understand it.


Thanks
 
You must log in or register to reply here.
Back
Top