VBA Mobile phone CSV file format. How to split it into two

[vbanewbie68]

Hello

I have a CSV file. One of the headers is Mobile Number. The first digit '0' is missing because of CSV Format. See below. I am trying to split it into this example 07445 25211.

Mobile number
7744525211
7744568256
7755775775
7822258548


The code below.

Sub tidyMobile(LastR)
   
    MobC = Application.Match("Mobile", msheet.Cells(1, 1).EntireRow, 0)   'Mobile Col
    mobArray = Split(" 7|07|447|+447|+07|44 7|+44 7|(44)7|(+44)7|(44) 7|(+44) 7", "|") 'Array of typical UK mobile starts
   
    For x = 2 To LastR
       
           
        MPhone = msheet.Cells(x, MobC).Value    'Mobile Number Provided
      
        'Tidy phone
        For I = 1 To 9
            If Left(MPhone, Len(mobArray(I))) = mobArray(I) Then
                MPhone = "07" & Mid(MPhone, Len(mobArray(I)) + 1)
                Exit For
            End If
        Next I
       
        If InStr(1, MPhone, " ", vbTextCompare) = 0 And Left(MPhone, 2) = "07" Then
            'Else if value in mobile, ensure space exists for those starting with 0
            MPhone = Left(MPhone, 5) & " " & Mid(MPhone, 6)
        End If
       
        msheet.Cells(x, MobC).Value = MPhone
     
              
     
    Next x

End Sub

Thank you

Regards

V

 

[alansidman]

A power query solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Mobile number", type text}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type", "Mobile number", Splitter.SplitTextByPositions({0, 4}, false), {"Mobile number.1", "Mobile number.2"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Position", "Custom", each "0" &[Mobile number.1]),
    #"Inserted Merged Column" = Table.AddColumn(#"Added Custom", "New Mobile", each Text.Combine({[Custom], [Mobile number.2]}, "-"), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Mobile number.1", "Mobile number.2", "Custom"})
in
    #"Removed Columns"

Book1
	A	B	C
1	Mobile number		New Mobile
2	7744525211		07744-525211
3	7744568256		07744-568256
4	7755775775		07755-775775
5	7822258548		07822-258548
Sheet1

 

[vbanewbie68]

Thanks for this. We have to use VBA to run a downloaded file please. I will bear that in future with Power Query.

Regards

 

[alansidman]

FYI--You can download files with PQ. It is just a case of selecting your source document from the menu.

 

[vbanewbie68]

With this VBA is given to me to run reports. I have to use this without PQ. We use this macro for that particular reports.

 

[vbanewbie68]

Would you able to tell me what went wrong with the VBA code lines?
V

 

[alansidman]

Patience. Wait for a VBA expert. Not my wheelhouse.

 

[vbanewbie68]

Thank you for your time on this which is really appreciated.

 

[vbanewbie68]

Hello

I have managed to fix the issue. I added |7 to go with this code line: mobArray = Split("447|+447|+07|44 7|+44 7|(44)7|(+44)7|(44) 7|(+44) 7|7", "|") 'Array of typical UK mobile starts

I am pleased that the result passed when I run the macro. The output displays 07744 525211 from the original 774452511. The code lines are working for the split and add nought in front of 7.

Hope this will help you for your future reference.

V