CODEWITHSUNDEEP
excelexcel-formula
gastone dereck
gastone dereck

Reputation: 25

How to extract pattern from a cell in excel

I have an excel file that contains data. There is one column that contains the description of payment and pay ID in each cell.
I want to extract only pay ID from each cell to the next column. pay ID contains 12 digits in the form of 991xxxxxxxxx.

Here is a sample information contained in some of the cells:

    Cell1 : 001 Cash Deposit -  0107 10:51:49 CMS NetworkID XXXPAY Control No 991110748686 KINFONCOMPANYLTD   !! From 
    
    Cell2 : 118 Bill Payment -  0107 11:59:20 agency @22910013899@GEPG_PAY billctrlNo 991110748329 Phone Number 255779206080 Name 
    
    Cell3 : 521 Utility Payments -  0107 14:56:26 MPESAC2B GEPG_PAY billctrlNo 991110275410 Phone Number 255755914055 Name!! From M-PESA-NMB COLLECTION ACCOUNT => 
    
    Cell4 : 372 Incoming Funds Transfer -  Sender's Ref: FT21091ZGDM9 => Ordering Customer: /103895100026 * 1  Remittance Info: 991110557255
    
    Cell5 : 118 Bill Payment -  0104 08:44:52 agency @31110037832@ _PAY billctrlNo 991110531647 Phone Number 255676770771 Name 
    
    Cell6 : 521 Utility Payments -  0104 12:20:48 CMS NetworkID GEPGPAY Control No 991110650984 RKPHARMACEUTICALSTZLTD   
    
    Cell7 : 372 Incoming Funds Transfer -  Sender's Ref: 005RTG210401016 => Ordering Customer: 0403901000 * VOCATIONAL TRAINING AUTHORITY => Remittance Info: 991110558261
    
    Cell8 : 521 Utility Payments -  0104 19:29:45 _PAY billctrlNo 991110290890 Phone Number 25475244199
    
    Cell9 : TMS GePG BIL:991110558959 AMANI CENTRE FOR STREET CHILDREN R REF:FB49141622529465
    
    Cell10 : REF 2408853105211627 GEPG BIL 991110855155 TO AC 01J1028467503
    
    Cell11 : CSM TISS GePG BIL:991110748443:REF:1622534049461198:001FTLC211520041:210601

Here is what i have done, but it's not effective on all cells.

=MID(B3,14,12)

Upvotes: 0

Views: 207

Answers (4)

Raphael Rohner
Raphael Rohner

Reputation: 21

Another possibility:

=LEFT(RIGHT(A1;LEN(A1)-FIND("991";A1));11)

works only if every pay id begins with 911 and there is no other "991" within the same row (especially before the pay id) and the number of digits is 11 (not 12!).

Upvotes: 0

ed2
ed2

Reputation: 1497

You could try something like:

=Mid(B3,Find("991",B3),12)

Upvotes: 1

Bastian
Bastian

Reputation: 518

Try first to find the starting location of your PayID in the cell and extract than the whole PayID:

=MID(B3; FIND("991"; B3); 11)

Upvotes: 0

Capt.Krusty
Capt.Krusty

Reputation: 627

Please try:

=MID(*CELL1*;FIND("991";*CELL1*;1);12)

"CELL1" refers to your cell that holds the text, for example "A1".

Upvotes: 1

Related Questions