0

I have a list of values as follows:

Maestro purchase 28.02.2019 18:54 Coop-4853 ZH Bombach Card number: 79090775                
Payment order               
Maestro purchase 27.02.2019 08:29 VBZ FRANKENTAL Card number: 79111775              
Maestro purchase 27.02.2019 17:48 VBZ SIHLQUAI/HB Card number: 79111775             
Maestro purchase 27.02.2019 08:29 VBZ FRANKENTAL Card number: 79111775              
Debit LSV+ VISECA CARD SERVICES SA              
Maestro purchase 27.02.2019 18:36 Coop-4853 ZH Bombach Card number: 79111775                
Maestro purchase 25.02.2019 14:32 VBZ ZURICH VERKEHRSB Card number: 79111776                
Maestro purchase 25.02.2019 16:21 Gemeinde Oberengstri Card number: 79111776                
Maestro purchase 23.02.2019 14:39 VBZ FRANKENTAL Card number: 79111776              
Maestro purchase 23.02.2019 15:27 VBZ MEIERHOFPLATZ Card number: 79111776               
Credit              
Maestro purchase 23.02.2019 15:21 Coop-1906 ZH Höngger Card number: 79111776                
Maestro purchase 22.02.2019 11:12 VBZ FRANKENTAL Card number: 79111776              
Maestro purchase 21.02.2019 07:36 VBZ FRANKENTAL Card number: 79111775  

I'd like to have one function that returns a list of all unique occurrences based on this rule:

IF the string starts with Maestro extract the string in between the first 34 chars and the last 22

[34 chars] "String to be extracted" [22 chars]

IF the string does not start with Maestro, then get the full string.

I was able to extract the strings based on the rule above with this function IFERROR(MID(A2,35,LEN(A2)-56),A2) but somehow I got stuck because I might have to wrap everything in an array formula or something.

If it helps here's a spreadsheet with the data:

https://docs.google.com/spreadsheets/d/1SPsZSVRMVZDDlYV7MovJ__0hkIMlXHTWg4Eq5_20gxw/edit?usp=sharing

1 Answer 1

0
=UNIQUE(ARRAYFORMULA(REGEXREPLACE(A2:A, 
 "Maestro purchase \d+.\d+.\d+ \d+:\d+ | Card number: \d+", "")))

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.