Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Rearrange data in cell

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Vancouver, Canada
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm importing data from an external database.
    It imports NAME as either a company name or personal name.
    Company name shows as:
    ABC Company LTD.
    Which is great!

    But when it imports a personal name, it's formated into one cell as:
    PULLEN/MICHAEL

    How can I automate it so I can rearrange the cell to show:
    MICHAEL PULLEN

    Any help is much appricated...

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,312
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    G'day MPullen

    I know there's a formula approach but a quick and dirty way would be to do a DataText to Columns - "/" as the delimiter.

    That would break your example into 2 columns (or more if the range has a cell with more than 1 "/").

    Basically you could then concatenate the order you want: something like =B2&" "&A2

    Then paste special the column as values and kill the 2 extra columns. You could use in if statement to check for blank cells if it breaks into 3 or more columns (say due to an entry like "Smith/Joe/Mary")

    Kinda manual I know, but quick and dirty.

    Hope that helps,
    Adam

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    Vancouver, Canada
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for you help.
    I've got something to work with now.

    But this spreadsheet is used everyday to pull lots of data.
    I was hoping to automate it.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-24 11:18, MPullen wrote:
    Thanks for you help.
    I've got something to work with now.

    But this spreadsheet is used everyday to pull lots of data.
    I was hoping to automate it.
    Why don't you change the query that produces the data set?

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,312
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey again,

    Here's a formula approach that will work as long as you only have one "/" in the column. If your data is in column A:

    In B2, =RIGHT(A2,LEN(A2)-LEN(LEFT(A2,FIND("/",A2)-1))-1)&" "&LEFT(A2,FIND("/",A2)-1)

    '--I'm pretty sure this can be tweaked to be a bit shorter.

    If you have some cells with more than one "/" then this complicates that formula a bit. I currently lack the caffeine levels in my bloodstream to play at that level. If you occasionally will end up with more than one "/"s then you may want to go with a VBA solution.

    Hope that helps out somewhat,
    Adam


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •