Required Excel Formula or VBA code to extract Numbers from String after special character "/" with 3 condition

[Harmandeep]

Hi Excel Experts,

I am trying to extract Numbers from String after special character "/" with Formula 'MID(A2, SEARCH("/",A2) + 1, SEARCH("/",A2,SEARCH("/",A2)+1) - SEARCH("/",A2) - 1)'. I have 3 diff types of String it works fine in 1 condition but failed to extract in rest 2 condition. I am attaching sample data table below & image for your reference.

Particulars	Result with Formula	Expected Result
IFT/CB0023519258/2000189024/MAHANADI COALFIELDS L	CB0023519258	CB0023519258
IMPS/P2A/235314533268/DIGITALI/IDFCBank/IMPSTXN	P2A	235314533268
NEFT/000001993682/2780032276/LAVYA MARKETING	000001993682	000001993682
RTGS/CBINR52022122810002779/18NEWDISTR/VINAYAK EN	CBINR52022122810002779	CBINR52022122810002779
TRANSFER/IC/25-980520196-1/ARPIT ENTERPRISE/Watch	IC	25-980520196-1
UPI/P2A/235423827250/KRISHAN K/Bank of B/UPI	P2A	235423827250
IMPS/234004143173	#VALUE!​	234004143173

 

[Harmandeep]

Hi Fluff can we add one more condition in this formula if "/" not found then it will split on "-" this character. E.g.-"NEFT-PUNBD22351007512-DISL8219658860-RAVI AUDIO VIDEO PROP RAVI DASS-8725210023" this kind of text is given instead of "MMT/IMPS/235111346866/DISL9142180005/MASTACOMMU".

 

[Fluff]

How about

=LET(a,TEXTSPLIT(A2,{"/","-"}),IF(COLUMNS(a)=1,a,TAKE(FILTER(DROP(a,,1),LEN(DROP(a,,1))>3),,1)))