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

 

[Fluff]

How about

Fluff.xlsm
	A	B
1	Particulars
2	IFT/CB0023519258/2000189024/MAHANADI COALFIELDS L	CB0023519258
3	IMPS/P2A/235314533268/DIGITALI/IDFCBank/IMPSTXN	235314533268
4	NEFT/000001993682/2780032276/LAVYA MARKETING	000001993682
5	RTGS/CBINR52022122810002779/18NEWDISTR/VINAYAK EN	CBINR52022122810002779
6	TRANSFER/IC/25-980520196-1/ARPIT ENTERPRISE/Watch	25-980520196-1
7	UPI/P2A/235423827250/KRISHAN K/Bank of B/UPI	235423827250
8	IMPS/234004143173	234004143173
Data

Cell Formulas
Range	Formula
B2:B8	B2	=LET(a,DROP(TEXTSPLIT(A2,"/"),,1),TAKE(FILTER(a,LEN(a)>3),,1))

 

[Harmandeep]

Thanks Fluff its working perfectly fine.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[jdellasala]

Why are PA2 and IC not correct? What makes the Expected Values correct for those instances? Will there be more similar oddities? Is it the length of the result, i.e. the result needs to be more than 3 characters?
Never mind. Nice solution @Fluff

 

[Skybluekid]

From what I can see there is no "Rule of Thumb" or consistant for a formula to work on. A possible solution would be to use TEXT TO COLUMNS or TEXTSPLIT,but you will have the same issue with unwanted columns

 

[Harmandeep]

Hi Fluff,

There is one more condition i need to fulfill with same formula as provided by you. When there is no special character. Sample table given below

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

Regards,
Harmandeep Singh

 

[Fluff]

How about

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

 

[Harmandeep]

Thanks now able get my Result.

 

[Fluff]

You're welcome.