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

 

[Bosquedeguate]

For your single digit values add a leading zero, e.g TB-1 change to TB-01, …

 

[TCyber]

For your single digit values add a leading zero, e.g TB-1 change to TB-01, …

Is it possible to do that in mass? There are over 300 combinations that I would have to manually put the zero's in.

 

[Bosquedeguate]

Is it possible to do that in mass? There are over 300 combinations that I would have to manually put the zero's in.

You should be able to do it with Excel’s Find and Replace functionality using a regular expression.

Are you familiar with regular expressions?

 

[TCyber]

You should be able to do it with Excel’s Find and Replace functionality using a regular expression.

Are you familiar with regular expressions?

I am and I thank you for your help! If I get stuck I will send another post.

 

[JEC]

Let's say the data is in column A. If you run the macro below, the output will be sorted as desired.

Sub jec()
 Dim ar, j As Long
 ar = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 2)
 With CreateObject("vbscript.regexp")
   .Pattern = "(TB-)(\d(?=\(|$))(.*)"
    For j = 1 To UBound(ar)
      ar(j, 2) = .Replace(ar(j, 1), "$10$2$3")
    Next
 End With
 Range("A1", Range("A" & Rows.Count).End(xlUp)) = Application.Index(Application.Sort(ar, 2), , 1)
End Sub

Note: This is not sorted yet

Book1
	A
1	TB-1
2	TB-1(1)
3	TB-1(2)
4	TB-11
5	TB-2
6	TB-3
7	TB-5
Sheet1

 

[JEC]

Using an Excel formula:

Book1
	A	B
1	Input	Output
2	TB-1	TB-1
3	TB-1(1)	TB-1(1)
4	TB-1(2)	TB-1(2)
5	TB-11	TB-2
6	TB-2	TB-3
7	TB-3	TB-5
8	TB-5	TB-11
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=SORTBY(A2:A8,SUBSTITUTE(REPLACE(A2:A8,MMULT(IFERROR(SEARCH({"-?(","-?^"},A2:A8&"^"),0),{1;1})+1,0,0),"0T","T"))
Dynamic array formulas.

 

[JEC]

One more formula option in column C

Book1
	A	B	C
1	Input	Output	Output 2
2	TB-1	TB-1	TB-1
3	TB-1(1)	TB-1(1)	TB-1(1)
4	TB-1(2)	TB-1(2)	TB-1(2)
5	TB-11	TB-2	TB-2
6	TB-2	TB-3	TB-3
7	TB-3	TB-5	TB-5
8	TB-5	TB-11	TB-11
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=SORTBY(A2:A8,SUBSTITUTE(REPLACE(A2:A8,MMULT(IFERROR(SEARCH({"-?(","-?^"},A2:A8&"^"),0),{1;1})+1,0,0),"0T","T"))
C2:C8	C2	=SORTBY(A2:A8,IF(LEN(SUBSTITUTE(MID(A2:A8,4,2),"(",""))=1,REPLACE(A2:A8,4,0,0),A2:A8))
Dynamic array formulas.

 

[TCyber]

Thank you both for your help is there somewhere that I would be able to use a wildcard in the formula? There are many different letter combinations is it possible to use the formula to get this type of outcome?

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

the letter acro's need to stay in that order so its like all of AB 1 -100 AC 1-100 and so on

 

[JEC]

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