トップ　>　汎用コード　>　都道府県と市区町村を分割

Sub Div_the_text()
    If TypeName(Selection) <> "Range" Then Exit Sub 'セルを選択していなければ終了
    If Selection.Areas(1).Columns.Count <> 1 Then Exit Sub '選択セル範囲が2列以上だと終了

    Dim varArray As Variant
    Dim varArea  As Variant
    Dim rngArea  As Range
    Dim i        As Long
    Dim n        As Long
    Dim v        As Variant
    
    varArray = Array("県", "道", "府", "都") '区切り文字列
    
    Set rngArea = Intersect(Selection.Areas(1), ActiveSheet.UsedRange).Resize(, 3)
    varArea = rngArea
    For i = 1 To UBound(varArea)
        For Each v In varArray
            n = InStr(1, CStr(varArea(i, 1)), CStr(v)) '区切り文字列の位置を検索
            If 0 < n And n < 5 Then
                varArea(i, 2) = Left$(CStr(varArea(i, 1)), n)
                varArea(i, 3) = Mid$(CStr(varArea(i, 1)), n + 1)
                Exit For
            End If
        Next
    Next
    rngArea = varArea
    Set rngArea = Nothing
End Sub