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.

	A	B
A1	INSP-6	INSP-00006
A2	INSP-16	INSP-00016
A3	INSP-116	INSP-00116
A4	INSP-1116	INSP-01116
A5	INSP-11116	INSP-11116
A6	INSP-6-PT	INSP-00006-PT
A7	INSP-16-PT	INSP-00016-PT
A8	INSP-116-PT	INSP-00116-PT
A9	INSP-1116-PT	INSP-01116-PT
A10	INSP-11116-PT	INSP-11116-PT

<tbody>
</tbody>

 

[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

	A​	B​
1​	INSP-6	INSP-00006
2​	INSP-16	INSP-00016
3​	INSP-116	INSP-00116
4​	INSP-1116	INSP-01116
5​	INSP-11116	INSP-11116
6​	INSP-6-PT	INSP-00006-PT
7​	INSP-16-PT	INSP-00016-PT
8​	INSP-116-PT	INSP-00116-PT
9​	INSP-1116-PT	INSP-01116-PT
10​	INSP-11116-PT	INSP-11116-PT

Sheet: Sheet1

 

[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

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​	INSP-6	INSP-00006
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	INSP-16	INSP-00016
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​	INSP-116	INSP-00116
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​	INSP-1116	INSP-01116
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​	INSP-11116	INSP-11116
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​	INSP-6-PT	INSP-00006-PT
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​	INSP-16-PT	INSP-00016-PT
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​	INSP-116-PT	INSP-00116-PT
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​	INSP-1116-PT	INSP-01116-PT
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​	INSP-11116-PT	INSP-11116-PT

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

 

[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.