Efficient Formula to Extract Text Between Second and Third Hyphen in Excel 2021

[irfman]

Hi,

I have a large dataset stored as string values in Column A. I need to extract the text that appears immediately after the second hyphen (-) and before the third hyphen (-).

Currently, I am using the following formula:
=MID(A2, FIND(CHAR(1), SUBSTITUTE(A2, "-", CHAR(1), 2)) + 1, FIND(CHAR(1), SUBSTITUTE(A2, "-", CHAR(1), 3)) - FIND(CHAR(1), SUBSTITUTE(A2, "-", CHAR(1), 2)) - 1)

However, this formula is lengthy, complicated, and not efficient for a large dataset.

I am using Excel 2021. Are there any simpler and more efficient formulas to achieve the same result?

I appreciate your usual support and assistance.

 

[Sam_D_Ben]

May be something like this for Excel 2021,

Book2
	A	B	C
1	String	Extract this	Formula
2	ACC-1-81-10103-1-5	81	81
3	ACC-1-605-11177-5-3	605	605
4	ACC-1-650-11177-1-4	650	650
5	ACC-1-568-11177-2-4	568	568
6	ACC-1-543-11177-1-4	543	543
7	ACC-1-615-11177-1-1	615	615
8	ACC-1-656-11177-1-0	656	656
9	ACC-1-583-11177-1-2	583	583
10	ACC-1-680-11177-1-0	680	680
11	ACC-1-622-11177-1-0	622	622
12	ACC-1-701-11177-1-6	701	701
13	ACC-1-641-11177-1-3	641	641
14	ACC-1-655-11177-1-9	655	655
15	ACC-1-570-11177-1-7	570	570
16	ACC-1-714-11177-1-1	714	714
17	ACC-1-701-11177-2-7	701	701
18	ACC-1-1592-11177-1-6	1592	1592
19	ACC-1-680-11177-2-1	680	680
Sheet2

Cell Formulas
Range	Formula
C2:C19	C2	=TRIM(MID(A2,FIND("-",A2,FIND("-",A2)+1)+1, FIND("-",A2,FIND("-",A2,FIND("-",A2)+1)+1) - FIND("-",A2,FIND("-",A2)+1) -1))

 

[Alex Blakenburg]

For a large dataset you might be better off with using Power Query.
This will be slightly more efficient and should be easier to follow.
Note: the double dash before the Mid converts it to a number. Remove them if you want the result as text.

=LET(hyphen2,FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),2)),
     hyphen3,FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),3)),
     --MID(A2,hyphen2+1,hyphen3-hyphen2-1))

 

[Tom.Jones]

Try

=TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",100)),3*100-99,100))

 

[Sam_D_Ben]

Try

=TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",100)),3*100-99,100))

 

[MARZIOTULLIO]

B2=IFERROR(LEFT(MID(A2:A100,7,8),SEARCH("-",MID(A2:A100,7,8))-1)+0,"")

Spill down formula

 

[Peter_SSs]

I am using Excel 2021.

Please update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

... a large dataset.

... Are there any ... more efficient formulas to achieve the same result?

Re more efficient and large data set. Just put this formula (adjusted for the number of possible rows) in B2. The efficiency/advantages are ..

1. There is no need to copy the formula down all the rows of the large data set, and
2. The results are returned as numbers, not text

25 03 24.xlsm
	A	B
1	String	Extract this
2	ACC-1-81-10103-1-5	81
3	ACC-1-605-11177-5-3	605
4	ACC-1-650-11177-1-4	650
5	ACC-1-568-11177-2-4	568
6	ACC-1-543-11177-1-4	543
7	ACC-1-615-11177-1-1	615
8	ACC-1-656-11177-1-0	656
9	ACC-1-583-11177-1-2	583
10	ACC-1-680-11177-1-0	680
11	ACC-1-622-11177-1-0	622
12	ACC-1-701-11177-1-6	701
13	ACC-1-641-11177-1-3	641
14	ACC-1-655-11177-1-9	655
15	ACC-1-570-11177-1-7	570
16	ACC-1-714-11177-1-1	714
17	ACC-1-701-11177-2-7	701
18	ACC-1-1592-11177-1-6	1592
19	ACC-1-680-11177-2-1	680
20
21
IRFMAN

Cell Formulas
Range	Formula
B2:B1000	B2	=IFERROR(--MID(SUBSTITUTE(A2:A1000,"-",REPT(" ",100)),200,100),"")
Dynamic array formulas.

 

[Alex Blakenburg]

@Peter_SSs, I considered using a dynamic array formula but since its a large dataset I wasn't sure of the performance implications.
It certainly seems to be the case that changing even one entry in column A makes the whole dynamic array "dirty" and it has to recalculate the whole array while in a standard formula it would only recalculate the one dependent formula.
For the current use case the difference seems too small to notice (I tried 50k records). Having said that the order of magnitude seemed to be that the Dynamic array formula took 10x as long as the standard formula.
I am sure it would start to add up if there were multiple array formulas in a workbook.

 

[Peter_SSs]

@Alex Blakenburg
Fair enough points Alex. I suppose I was more thinking about efficiency for the user, not have to copy the formula down lots of rows.

We do not know just how big the OP's "large dataset" might be but I have done some testing with 10,000 rows of data similar to the sample given.
I tested the formulas from posts 1, 2, 3, 3b*, 4 (& later 9)

* 3b - I modified your formula slightly since the request (& the OP's working formula) was to extract the text from the string. Your conversion to numerical takes some time (extremely small) & also would drop any leading zero if there were any. So to compare the text extraction I simply removed the "--" from your formula

=LET(hyphen2,FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),2)),
     hyphen3,FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),3)),
     MID(A2,hyphen2+1,hyphen3-hyphen2-1))

Post 1 was definitely the slowest and 3b (your formula modified as above) was the quickest but really there was very little difference between that and @Tom.Jones formula in Post 4.

However, in looking at Post 4, I note that the REPT function can be a little slow, more so the more any text is repeated.
So, assuming data like the samples, that formula could be sped up somewhat by reducing the number of space characters repeated. Say ..

=TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",10)),20,10))

Even more improvement though is achieved (again assuming the sample data) by actually coding in the space characters

=TRIM(MID(SUBSTITUTE(A2,"-","          "),20,10))

Calling this last formula the Post 9 formula, here are my timing results.

irfman.xlsm
	J	K	L	M	N	O	P
1		Post 1	Post 2	Post 3	Post 3b	Post 4	Post 9
2	Time (secs) to recalculate 10,000 rows	0.0624	0.04468	0.04384	0.03487	0.0372	0.02735
3	Rank	6	5	4	2	3	1
4	Fastest compared to 2nd fastest						78.43%
5	Fastest compared to original						43.83%
Sheet1

Cell Formulas
Range	Formula
K3:P3	K3	=RANK(K2,$K2:$P2,1)
P4	P4	=P2/N2
P5	P5	=P2/K2

 

[Alex Blakenburg]

Thanks @Peter_SSs, that was really interesting.
I only compared the identical dynamic array version to the non-array version.
I was wondering about how the REPT function performed.