none
Excel macro to split an entry RRS feed

  • Question

  • Hello.

    I've tried searching the database but I haven't found an answer to what I'm sure is a simple problem for "real" excel macro users.

    I have long lists of names and telephone numbers that have been filled in in a single field and I want to take the number out and put it in a second field...

    It looks like this:

    AXXXXXXN Cxxxxxxxn 03 20 XX XX 96 

    AXXXXXXY  Gxxxxs 0X 22 XX XX 73

    AXXXXXXE Gxxxxxl, 0X 62 XX XX 47

    AMPERE Claude 0X 20 98 64 67

    AXXXXXXR Cxxxxxxxe 0X 73 XX XX 02

    I record my macro using "relative references" just by electing the telephone number and cutting and pasting it to the next column.

    The macro (or at least one of the many I've tried) looks like this:

    Sub Macro1()
    '
    ' Macro1 Macro
    '
    ' Touche de raccourci du clavier: Ctrl+w
    '
        ActiveCell.Select
        ActiveCell.FormulaR1C1 = "AXXXY  Gxxxxs "
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveSheet.Paste
    End Sub

    So all that happens when I go to the next entry is that I get the same number and the same name in the new line... I've sometimes managed to build the macro not to change the name and then it only puts up the first referenc number...

    I'm sure I'm doing something wrong at the "relative references" level but I haven't been able to fathom out what...

    Any clues would be gratefully accepted

    Keith Braithwaite

    Saturday, October 7, 2017 2:12 PM

All replies

  • Keith

    A solution to what you have asked is below.  However, it does require that the phone numbers are all of the same length.  So it would not work for phone numbers that include the international code, or for phone numbers not in the French format.  All the code does is take the last 14 letters / numbers in the selected cell and move them to the next cell to the right.

    Sub ShiftNumbers()

    Dim strDetails As String

    Dim strPhone As String

        strDetails = Selection.Value 'Value of the whole cell

        strPhone = Right(strDetails, 14) 'value of the rightmost 14 characters

        Selection.Offset(0, 1).Value = strPhone 'put the phone number into the cell one to the right

        strDetails = Left(strDetails, Len(strDetails) - 15) 'Set the selected cell to be all but the last 15 characters

        Selection.Value = strDetails 'Put the new, truncated value back into the selected cell.   

    End Sub

    The problem with recording your macro is that it did not record the “Cut” command.  So the item that you copied while recording remains on the clipboard for subsequent runs of the macro.

    Hope this helps

    AndyC

    Saturday, October 7, 2017 11:13 PM
  • Hi,

    Please find out the regularity about name and phone number and provide it.
    For example, 
    1) there are always two space(s) before phone number
    2) phone number must begins with a numeric character (0-9) and names do not have numeric characters

    If some regularity exist in a cell, we can determine the start and stop position of phone number. 

    Regards,

    Ashidacchi

    Monday, October 9, 2017 12:40 AM