How to remove +44 from mobile phone VBA?

[vbanewbie68]

Hi

How to remove +44 from mobile phone. Please see my screenshot

This is my vba:
Imprt.Cells(x, OptInSMS).Value = Imprt.Cells(x, OptInSMS).Value
Thanks in advance.
Regards
Rej

 

[johnnyL]

How about:

    Dim Cel As Range
'
    For Each Cel In Range("Z1:Z" & Range("Z" & Rows.Count).End(xlUp).Row)
        If Left(Cel.Value, 3) = "+44" Then Cel.Value = Right(Cel.Value, Len(Cel.Value) - 3)
    Next

 

[vbanewbie68]

Hi JohnnyL

Thank you for this and your time on this matter. Please see below this is what I have on the macro. The question is that how you convert your lines convert into this lines please?

OptInSMSNumber = WorksheetFunction.Match("OptInSMSNumber", Import.Cells(1, 1).EntireRow, 0)

Import.Cells(x, OptInSMSNumber).Value =

Best regards

V

 

[johnnyL]

Can you post more of your code?

 

[vbanewbie68]

Hi Sir

It works! I have managed to remove +44 which is great but zero is missing. I need to add '0' so what is the best way for adding zero at the front of the mobile number please? Please see my screenshot below.

'Mobile phone format +44
If Left(Cells(x, OptInSMSNumber).Value, 3) = "+44" Then

Cells(x, OptInSMSNumber).Value = Right(Cells(x, OptInSMSNumber).Value, Len(Cells(x, OptInSMSNumber).Value) - 3)

End If

Best regards
V

 

[johnnyL]

Cells(x, OptInSMSNumber).Value = "0" & Right(Cells(x, OptInSMSNumber).Value, Len(Cells(x, OptInSMSNumber).Value) - 3)

 

[vbanewbie68]

Cells(x, OptInSMSNumber).Value = "0" & Right(Cells(x, OptInSMSNumber).Value, Len(Cells(x, OptInSMSNumber).Value) - 3)

Thank you so much sir! I am pleased to tell you that it is working with adding zero at the front of the mobile phone. Thanks again for your kind help on this matter which is greatly appreciated

Best regards

V

 

[johnnyL]

You are welcome @vbanewbie68. Glad to help.