Getting Acronyms with numbers to sort in number order

[TCyber]

Hello, I am trying to make a pivot table with a large amount of different acronyms/number combinations. The problem is I have numbers like TB-1, TB-11, TB-2(3), DW-5(7), DW-6. I really need them to sort in the number after the dash. So it would look in a column in the order like this TB-1 | TB-1(1) | TB-2..............TB-10 | TB-11 and so on. right now it puts 11 before 2, there are 100's of these I need to verify and this would make it so much easier.

Office 365

 

[TCyber]

How about BF2(3)? Where is the hyphen?

Typo I apologize

Book1
	A
1	AX-8
2	AX-10
3	BF-1
4	BF-2
5	BF-2(3)
6	BF-20(1)
Sheet1

 

[JEC]

Book1
	A	B
1	input	outpout
2	AX-8	AX-8
3	AX-10	AX-10
4	BF-1	BF-1
5	BF-2	BF-2
6	BF-2(3)	BF-2(3)
7	BF-20(1)	BF-20(1)
Sheet2

Cell Formulas
Range	Formula
B2:B7	B2	=SORTBY(A2:A7,IF(LEN(SUBSTITUTE(MID(A2:A7,4,2),"(",""))=1,REPLACE(A2:A7,4,0,0),A2:A7))
Dynamic array formulas.

 

[JEC]

And for the VBA, change the regex to

.Pattern = "(.*-)(\d(?=\(|$))(.*)"

 

[TCyber]

How about BF2(3)? Where is the hyphen?

Typo, I apologize

Book1
	A
1	AX-8
2	AX-10
3	BF-1
4	BF-2
5	BF-2(3)
6	BF-20(1)
Sheet1

 

[TCyber]

It worked thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[JEC]

You're welcome, cheers!

 

[TCyber]

Last question, is there any specific process I need to do to make sure that the data sorted reflects when I put it into a pivot table?

 

[JEC]

If you want to make sure that everything is sorted properly in the pivot table, you could use this formula where you leave the "0"
Are you using the VBA or the formula?

Book1
	A	B
1	input	output
2	BF-20(1)	AX-08
3	BF-2	AX-10
4	BF-1	BF-01
5	AX-10	BF-02
6	BF-2(3)	BF-02(3)
7	AX-8	BF-20(1)
Sheet2

Cell Formulas
Range	Formula
B2:B7	B2	=SORT(IF(LEN(SUBSTITUTE(MID(A2:A7,4,2),"(",""))=1,REPLACE(A2:A7,4,0,0),A2:A7))
Dynamic array formulas.

 

[TCyber]

If you want to make sure that everything is sorted properly in the pivot table, you could use this formula where you leave the "0"
Are you using the VBA or the formula?

Book1
	A	B
1	input	output
2	BF-20(1)	AX-08
3	BF-2	AX-10
4	BF-1	BF-01
5	AX-10	BF-02
6	BF-2(3)	BF-02(3)
7	AX-8	BF-20(1)
Sheet2

Cell Formulas
Range	Formula
B2:B7	B2	=SORT(IF(LEN(SUBSTITUTE(MID(A2:A7,4,2),"(",""))=1,REPLACE(A2:A7,4,0,0),A2:A7))
Dynamic array formulas.

Just the formula, never heard of VPA before getting here this morning. New job requires some deep dive knowledge of Excel. No offense but I have avoided office products for years. I was always behind the scenes as a cyber/developer so I have some understanding of code. Any links or info you could provide would be much appreciated. I know you do not want to answer the 50,000 questions I have in excel.

 

[TCyber]

Just the formula, never heard of VPA before getting here this morning. New job requires some deep dive knowledge of Excel. No offense but I have avoided office products for years. I was always behind the scenes as a cyber/developer so I have some understanding of code. Any links or info you could provide would be much appreciated. I know you do not want to answer the 50,000 questions I have in excel.

I was a past sys admin and clicking until you get the outcome you want does not work that great in Excel.