Fixing the names for sorting in Excel

[sakrams]

Good Afternoon Excel Gurus,


Need help from the gurus with a quick formula to achieve the following result.


Column A has Names that I am trying to change to match the results as shown in column B.

[TABLE="width: 292"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]INSP-6[/TD]
[TD]INSP-00006[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]INSP-16[/TD]
[TD]INSP-00016[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]INSP-116[/TD]
[TD]INSP-00116[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]INSP-1116[/TD]
[TD]INSP-01116[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]INSP-11116[/TD]
[TD]INSP-11116[/TD]
[/TR]
[TR]
[TD]A6[/TD]
[TD]INSP-6-PT[/TD]
[TD]INSP-00006-PT[/TD]
[/TR]
[TR]
[TD]A7[/TD]
[TD]INSP-16-PT[/TD]
[TD]INSP-00016-PT[/TD]
[/TR]
[TR]
[TD]A8[/TD]
[TD]INSP-116-PT[/TD]
[TD]INSP-00116-PT[/TD]
[/TR]
[TR]
[TD]A9[/TD]
[TD]INSP-1116-PT[/TD]
[TD]INSP-01116-PT[/TD]
[/TR]
[TR]
[TD]A10[/TD]
[TD]INSP-11116-PT[/TD]
[TD]INSP-11116-PT[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

Here's one way, maybe not the best way, but works for the sample data provided.

=SUBSTITUTE(A1,"-","-"&REPT("0",10+((RIGHT(A1,2)="PT")*3)-LEN(A1)),1)

I wonder if there are more nuances to this than you have described in your OP ?

 

[Yongle]

Or perhaps a much longer array formula

=SUBSTITUTE(A1,
TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),"")),
TEXT(TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),"")),
"00000"))

To enter an array formula commit with {CTRL}{SHIFT}{ENTER}
The formula will look like this (but it will not work if you try to enter { } manually!)
{=SUBSTITUTE(A1,
TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),"")),
TEXT(TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),"")),
"00000"))}

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]INSP-6[/td][td]INSP-00006[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]INSP-16[/td][td]INSP-00016[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]INSP-116[/td][td]INSP-00116[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]INSP-1116[/td][td]INSP-01116[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]INSP-11116[/td][td]INSP-11116[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]INSP-6-PT[/td][td]INSP-00006-PT[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]INSP-16-PT[/td][td]INSP-00016-PT[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]INSP-116-PT[/td][td]INSP-00116-PT[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]INSP-1116-PT[/td][td]INSP-01116-PT[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]INSP-11116-PT[/td][td]INSP-11116-PT[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[sakrams]

Brilliant. This worked like a charm. Thanks a million Gerald. Will save me a lot of time.

Here's one way, maybe not the best way, but works for the sample data provided.

=SUBSTITUTE(A1,"-","-"&REPT("0",10+((RIGHT(A1,2)="PT")*3)-LEN(A1)),1)

I wonder if there are more nuances to this than you have described in your OP ?

 

[sakrams]

Tried this and committed with {CTRL}{SHIFT}{ENTER}, however, i am getting an error. I am using Excel 2010, could that be the reason?

Thanks a million for your reply.

Or perhaps a much longer array formula

=SUBSTITUTE(A1,
TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),"")),
TEXT(TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),"")),
"00000"))

To enter an array formula commit with {CTRL}{SHIFT}{ENTER}
The formula will look like this (but it will not work if you try to enter { } manually!)
{=SUBSTITUTE(A1,
TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),"")),
TEXT(TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),"")),
"00000"))}

Excel 2016 (Windows) 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​

[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​

[/TD]
[TD]INSP-6[/TD]
[TD]INSP-00006[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

[/TD]
[TD]INSP-16[/TD]
[TD]INSP-00016[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​

[/TD]
[TD]INSP-116[/TD]
[TD]INSP-00116[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​

[/TD]
[TD]INSP-1116[/TD]
[TD]INSP-01116[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

[/TD]
[TD]INSP-11116[/TD]
[TD]INSP-11116[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​

[/TD]
[TD]INSP-6-PT[/TD]
[TD]INSP-00006-PT[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​

[/TD]
[TD]INSP-16-PT[/TD]
[TD]INSP-00016-PT[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​

[/TD]
[TD]INSP-116-PT[/TD]
[TD]INSP-00116-PT[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​

[/TD]
[TD]INSP-1116-PT[/TD]
[TD]INSP-01116-PT[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​

[/TD]
[TD]INSP-11116-PT[/TD]
[TD]INSP-11116-PT[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Yongle]

i am getting an error. I am using Excel 2010, could that be the reason?

Yes - TEXTJOIN is a recent function

 

[Gerald Higgins]

Hi Yongle - I don't have access to TEXTJOIN and I'm curious . . . what does it do ?

Maybe we can do something similar with some other function(s) ?

 

[Yongle]

@Gerald Higgins
It concatenates values with option to add delimiter and option to ignore empty cells
https://www.techonthenet.com/excel/formulas/textjoin.php
-

 

[Gerald Higgins]

Thanks Yongle, I can see how that could be useful.