Formula to extract and concatenate

[mmorris1965]

Seeking a nested formula to extract specific numbers from the cells in column "A" and concatenate with data in column "B". Because some of the cells contain leading alpha characters and some do not, I am having trouble using the Mid function in my formula. I've shown the desired result in column "C".

Any assistance would be appreciated.

 

[MARK858]

As I stated, I am very surprised at the results (I am treating the 2nd run on post 3 as a freak result btw).
Does tell me that I definitely need to use the Let function more on my own work though.

 

[Peter_SSs]

I am very surprised at the results

I am not surprised. Not because of the LET function, but because of the REPT function. REPT(" ",100) has to concatenate 100 characters which is (relatively) time-consuming.

I agree that speed is pretty irrelevant here as I'm sure they are all fast enough & not that much different to each other, but this was about 15% faster for me that the post 5 formula.
(In the normal course of events I would most probably use a formula like Rick's though. )

20 12 30.xlsm
	A	B	C
2	-/01/-/1-6120/RECPT	1015	1-6120-1015
3	CCH/01/-/01-6230/REC	1017	01-6230-1017
Test speed

Cell Formulas
Range	Formula
C2:C3	C2	=LET(first,FIND("~",SUBSTITUTE(A2,"/","~",3))+1,MID(A2,first,FIND("/",A2,first)-first)&-B2)

 

[MARK858]

but this was about 15% faster for me that the post 5 formula

Came out as 12.809 milliseconds on a single run on the same data for me against the Average run of 18.1126 for post 5.

 

[Peter_SSs]

Came out as 12.809 milliseconds on a single run on the same data for me against the Average run of 18.1126 for post 5.

I'm guessing that SUBSTITUTE must be a bit slow too as avoiding that function altogether increases my speed by another approx. 15%

20 12 30.xlsm
	A	B	C
2	-/01/-/1-6120/RECPT	1015	1-6120-1015
3	CCH/01/-/01-6230/REC	1017	01-6230-1017
Test speed

Cell Formulas
Range	Formula
C2:C3	C2	=LET(first,FIND("/",A2,FIND("/",A2,FIND("/",A2,FIND("/",A2)+1)+1))+1,MID(A2,first,FIND("/",A2,first)-first)&-B2)