Split and Replace First Word of A String in VBA

Question

I have this date data for example: Monday, September 9, 2024. I wish to replace the day into my language, for ex: monday to senin, tuesday to selasa, and so on. I've tried creating the code, but it doesn't give any results.

Dim iz As Integer
    Dim lsw As Long
    
    lsw = ThisWorkbook.Sheets("Sheet1").Range("G" & Rows.Count).End(xlUp).Row
    
    For iz = 2 To lsw
    Range("G" & iz) = Replace(LCase(Range("G" & iz)), "Monday", "Senin")
    Range("G" & iz) = Replace(LCase(Range("G" & iz)), "Tuesday", "Selasa")
    Range("G" & iz) = Replace(LCase(Range("G" & iz)), "Wednesday", "Rabu")
    Range("G" & iz) = Replace(LCase(Range("G" & iz)), "Thursday", "Kamis")
    Range("G" & iz) = Replace(LCase(Range("G" & iz)), "Friday", "Jumat")
    Range("G" & iz) = Replace(LCase(Range("G" & iz)), "Saturday", "Sabtu")
    Range("G" & iz) = Replace(LCase(Range("G" & iz)), "Sunday", "Minggu")

Also, I wish to change the date into this format Monday, September 9, 2024 to Senin, 9 September 2024

Could anyone give me a feedback if this is possible.

Answer 1

International Date Format

• To show the dates in Indonesian format, prefix the desired date format with either [$-id-ID] or [$-421] (US is [$-en-US] or [$-409]).

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim rg As Range: Set rg = ws.Range("G2", ws.Cells(ws.Rows.Count, "G").End(xlUp))

rg.NumberFormat = "[$-id-ID]dddd, d mmmm yyyy" ' or "[$-421]dddd, d mmmm yyyy"