Results 1 to 2 of 2
  1. #1
    Charles Notley is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Apr 2017
    Posts
    1

    Excel macro to rearrange cell information

    I need to reorganize the data in an Excel spreadsheet column, which comes to me this way:

    Charles Notley MI CSN

    In other words: first name [space] surname [space] state (two letters) [space space] three letters.

    I need to have the data arranged this way:

    MI Charles Notley CSN

    In other words: state (two letters) [space] first name (of variable length) [space] surname (variable length) [space space] three letters

    This could be a manual operation for each row in the spreadsheet column, in which case I would have to run the macro for each row in the column.

    I conceive the steps to be something like this, but I don’t know how to code it:


    1. Start at first letter in the text string.
    2. Highlight a variable length of letters, moving from left to right, until and including reach a space.
    3. CTL X highlighted section from step 2, including the space.
    4. Indent left.
    5. Right arrow 4 times.
    6. CTL V the highlighted section from step 2.
    7. Stop.


    In other words, it would look like this, step-by-step:

    Charles Notley MI CSN
    Charles Notley MI CSN
    MI CSN


    MI Charles Notley CSN

    Can this be done? How much extra trouble would it be to cause this procedure to automatically continue, working its way down a variable number of rows in a column and stop when it has completed the last row of the column which has data in it?

    Thanks

    Charles Notley

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Here is an Excel Macro that will make those changes for all entries in column A down to the last cell with data in it:
    Code:
    Sub MyDataSplit()
    
        Dim lastRow As Long
        Dim myRow As Long
        Dim LString As String
        Dim LArray() As String
        
        Application.ScreenUpdating = False
    
    '   Find last row in column A
        lastRow = Cells(Rows.Count, "A").End(xlUp).Row
        
    '   Loop through all cells in column A
        For myRow = 1 To lastRow
            LString = Cells(myRow, "A")
            LArray = Split(LString, " ")
            Cells(myRow, "A") = LArray(2) & " " & LArray(0) & " " & LArray(1) & " " & LArray(3)
        Next myRow
    
        Application.ScreenUpdating = True
        
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Data from Excel has Alt enter in a cell
    By newbee in forum Reports
    Replies: 1
    Last Post: 03-14-2013, 12:09 PM
  2. Pulling Excel Cell Information via Dynamic URL
    By brenthardy in forum Programming
    Replies: 1
    Last Post: 01-11-2012, 07:16 PM
  3. Importing cell notes from excel
    By timmy in forum Import/Export Data
    Replies: 1
    Last Post: 03-12-2011, 01:34 PM
  4. Rearrange name in Table
    By Brian62 in forum Access
    Replies: 5
    Last Post: 09-15-2009, 03:31 PM
  5. Can we post Access cell data to Excel cell properties?
    By Zethro in forum Import/Export Data
    Replies: 1
    Last Post: 12-13-2005, 08:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums