none
Remove unnecessary characters from a text RRS feed

  • Question

  • I have a long list of mobile numbers that are like (0912)-6862723. I want to upload a list of this mobile number list that should have no extra character like ( or -. The mobile number should be like this 09126862723. I tried CLEAN() function, special paste and some other ways but I haven't get my result. How can I convert this text (0912)-6862723 to 09126862723.

    Regards,


    Karim Vaziri Regards,

    Friday, October 21, 2016 8:16 PM

All replies

  • With a phone number in cell A2, you can use this formula:

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")",""),"-","")

    This formula can be filled down.

    Alternatively, select the entire range and run the following macro:

    Sub CleanPhoneNumbers()
        Dim cel As Range
        Application.ScreenUpdating = False
        Selection.NumberFormat = "@"
        For Each cel In Selection
            cel.Value = Replace(Replace(Replace(cel.Value, "(", ""), ")", ""), "-", "")
        Next cel
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, October 21, 2016 8:44 PM
  • Hi kvaziri,

    Did the suggestion from Hans work for you? If you have any updates about your issue, please feel free to let us know.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Saturday, November 5, 2016 6:17 AM