Create a hyperlink automatically based on cell value

[paun_shotts]

I have a spreadsheet where in column H the user is required to put in a con-note tracking number for a return parcel.
I was wondering if there is a way, either with a macro or a formula to convert this connote into a hyperlink.
The con-note is created with Startrack most of the time.
The con-note number is 12 characters long
The con-note always starts with "8KSZ"

I would like a solution that checks the data in column H, if the data there is 12 character long and starts with 8KSZ
Then
Convert that cell to a hyperlink pointing to the following location:
https : // auspost.com.au/mypost/track/#/details/"DATA IN CELL IN COLUMN H"
Without the spaces after https
If the data in column H is not 12 characters long and does not start with 8KSZ then leave the data in that cell as it is (no hyperlink)

QAD0702 GRA Log Records (Since May 2009) (Rev 4, 12-08-2022).xlsx
	A	B	C	D	E	F	G	H
1	GRA#	GRA Raised Date	Procedure/Case Date	Initial	Customer / Supplier	CAR/Repair # [if applicable]	Details (Product#, Serial#, Lot#, etc.) please include: quantities, brand name, etc.	Con Note #
2	2201001	4/01/2022		CB	Gold Coast Univ Hospital		1 x 94202 return for credit	8KSZ00014786
3	2201002	4/01/2022		CB	Wollongong Public Hospital		1 x 6001387	8KSZ00014794
4	2201003	4/01/2022		LF	Sir Charles Gairdner		Return of loan stock	8KSZ00014809
5	2201004	4/01/2022		LF	Sir Charles Gairdner		Return of loan stock	8KSZ00014810
2022

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=$J1="Yes"	text	NO
C2:C47,C49:C64,C66:C1048576	Expression	=$J2="Yes"	text	NO
B1	Expression	=$J1="Yes"	text	NO
K1:Q2,A2:B47,K76:L114,L115:L127,L128:Q129,K130:Q413,K415:Q430,K414,M414:Q414,K432:Q450,K431,M431:Q431,K455,M455:Q455,K462:K465,M462:Q465,K456:Q456,K452:Q454,K451,M451:Q451,K469:K470,K458:Q461,K457,M457:Q457,K3:L60,A49:B64,K62:L74,N3:Q127,A1,K466:Q468	Expression	=$J1="Yes"	text	NO

 

[paun_shotts]

@John_w I have seen you posting solutions to similar requests.. possibly you could help me with this one?

 

[John_w]

Put this in the worksheet's sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count = 1 And Target.Column = 8 And Target.Row >= 2 Then
        If Left(Target.Value, 4) = "8KSZ" And Len(Target.Value) = 12 Then
            Target.Hyperlinks.Add Target, "https://auspost.com.au/mypost/track/#/details/" & Target.Value, , , Target.Value
        End If
    End If
  
End Sub

 

[paun_shotts]

Put this in the worksheet's sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count = 1 And Target.Column = 8 And Target.Row >= 2 Then
        If Left(Target.Value, 4) = "8KSZ" And Len(Target.Value) = 12 Then
            Target.Hyperlinks.Add Target, "https://auspost.com.au/mypost/track/#/details/" & Target.Value, , , Target.Value
        End If
    End If
 
End Sub

Thank you very much sir!