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]

Maybe
Edit: formula incorrect for row 3... deleted

 

[mrshl9898]

Not pretty, but assuming the value in A is after the 3rd /

=LEFT(MID(A2,FIND(CHAR(160),SUBSTITUTE(A2,"/",CHAR(160),3))+1,LEN(A2)),FIND("/",MID(A2,FIND(CHAR(160),SUBSTITUTE(A2,"/",CHAR(160),3))+1,LEN(A2)))-1)&"-"&B2


*similar to Marks, but looking at everything between the 3rd and 4th /.

 

[MARK858]

Another adaption...

=TRIM(MID(A2,FIND("#",SUBSTITUTE(A2,"/","#",3))+1,FIND("~",SUBSTITUTE(A2,"/","~",4))-FIND("~",SUBSTITUTE(A2,"/","~",3))-1))&"-"&B2

All very similar

Book2
	A	B	C
2	-/01/-/1-6120/RECPT	1015	1-6120-1015
3	CCH/01/-/01-6230/REC	1017	01-6230-1017
Sheet2

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

 

[MARK858]

Or another option if you have 365 and the Let function

Book2
	A	B	C
2	-/01/-/1-6120/RECPT	1015	1-6120-1015
3	CCH/01/-/01-6230/REC	1017	01-6230-1017
Sheet2

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

 

[Rick Rothstein]

Give this formula a try...

=TRIM(LEFT(RIGHT(SUBSTITUTE(A2,"/",REPT(" ",100)),200),100))&"-"&B2

 

[MARK858]

Some timing results I find a little surprising...
I expected
The formula in post 3 to be faster than post 4
The Let formula in post 5 to be faster than both
Rick's formula in post 6 to be competing with / or beating the Let formula.

The results are below (timed using FastExcel on 10,000 rows of data, times in milliseconds) the last row is the Average.
Not that it makes much difference as there is only a range of 6 milliseconds between them (and I only really wanted to test the Let function).

Post 4	Post 3	Post 5	Post 6
22.461	24.451	18.853	26.286
22.451	28.841	18.375	25.679
23.573	25.637	17.534	22.854
22.521	24.768	18.242	22.903
21.992	24.952	17.559	23.812
22.5996	25.7298	18.1126	24.3068

 

[Rick Rothstein]

Rick's formula in post 6 to be competing with / or beating the Let formula.

I'm confused. Unless I don't understand your table, it seems to show that my formula never beats the Let formula. What am I missing? I agree the 6 ms difference means that all formulas are virtually the same speed-wise (as usually tends to be the case, mine is shorter though), but I wanted to understand your statement.

 

[MARK858]

I'm confused. Unless I don't understand your table, it seems to show that my formula never beats or even comes close to tying the Let formula. What am I missing?

The top paragraph is what I was expecting, not a summary of the results.

 

[Rick Rothstein]

The top paragraph is what I was expecting, not a summary of the results.

Ah, I just reread you message and now understand what you were saying (sometimes I can be dense).