Change cell property (formula or VBA)

[Belleke]

How to change entries like this
123456789A024321 into 1234.56.789.A02.4321.
Please advise.

 

[NARAYANK991]

Hi ,

Can you confirm whether the split will always be :

1. 4 digits on the left
2. Period
3. Two digits
4. Period
5. Three digits
6. Period
7. Letter followed by 2 digits
8. Period
9. 4 digits on the right

If not , upload a workbook with as much data as possible , along with the expected outputs.

Narayan

 

[Belleke]

Hi Narayan.
Thank you for the reply.
Yes I can confirm, the split is always the same.

 

[bosco_yip]

Formula way,

=REPLACE(REPLACE(REPLACE(REPLACE(A1,5,,"."),8,,"."),12,,"."),16,,".")&"."

Regards
Bosco

 

[Belleke]

Dear Bosco
Thank you, perfect formula
Works great.
Regards,
Ps: if someone has a VBA solution, please advice, then I can test both.
Thank you Narayan and Bosco.

 

[SirJB7]

Hi, Belleke!
Give a try to this code:

Option Explicit

Sub X()
    Debug.Print sCarlsbergFunction("123456789A024321")
End Sub

Function sCarlsbergFunction(psInput As String) As String
    '123456789A024321 into 1234.56.789.A02.4321.
    ' constants
    Const ksPos = ",4,2,3,3,4"
    ' declarations
    Dim sPos() As String
    Dim I As Integer, J As Integer, K As Integer, A As String
    ' start
    sPos() = Split(ksPos, ",")
    J = 0
    A = ""
    ' process
    For I = 1 To UBound(sPos)
        K = CInt(sPos(I))
        A = A & Mid(psInput, J + 1, K) & "."
        J = J + K
    Next I
    ' end
    sCarlsbergFunction = A
End Function

Regards!

 

[Belleke]

Thank you SirJB7