+ Reply to Thread
Results 1 to 11 of 11

Excel 2007 : Excel 2007 How to move trailing numbers into the adjacent column

Hybrid View

  1. 04-11-2011, 07:35 AM #1
    markDuffy
    markDuffy is offline
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2021
    Posts
    318

    Excel 2007 How to move trailing numbers into the adjacent column

    Hi
    I have a column of business names. Some of them have trailing numbers.
    Kangaroo 935
    Kangaroo Express 926
    Rite Aid Store 11360
    Short Stop 68

    I want to move these to an adjacent column.

    Kangaroo 935
    Kangaroo Express 926
    Rite Aid Store 11360
    Short Stop 68

    Previously these had a # which I converted to a comma copied to a text file and then recreated an excel file.
    Any help would be greatly received.
    Also please tell me how to mark the question as solved as I had an issue solved the other day.

    Mark
    Last edited by markDuffy; 04-11-2011 at 11:16 AM.
    Reply With QuoteRegister To Reply
  2. 04-11-2011, 07:43 AM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,099

    Re: Excel 2007 How to move trailing numbers into the adjacent column

    If the data is in column A, starting in row 1, then, in cell B1, put the formula:

    =SUBSTITUTE(A1," ",",",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))

    and drag down.

    This converts the last space to a comma (,)

    Then select the data in column B and Copy and Paste Special | Values to convert the formulae to text values. After that, you can use Data | Text to Columns delimited by a comma to split the data into two columns.

    Regards
    Last edited by TMS; 04-11-2011 at 12:16 PM.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 04-11-2011, 11:35 AM #3
    markDuffy
    markDuffy is offline
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2021
    Posts
    318

    Re: Excel 2007 How to move trailing numbers into the adjacent column

    Hi Thanks for all this it is excellent.
    I realize that my notes for you were not accurate enough. I did not include those which did not have a number or that only had one word. I have sorted these out by using the dodgy ones from the original. Just in case there is an easier way for the next one I have .

    Kangaroo 935
    Kangaroo Express 926
    Rite Aid Store 11360
    Short Stop 68
    Fairway Exxon
    Cruizers
    What I got.
    Kangaroo 935
    Kangaroo Express 926
    Rite Aid Store 11360
    Short Stop 68
    Fairway Exxon
    "Value"

    Which was fantastic as my column was 37,000.

    Thanks again.

    Mark
    Reply With QuoteRegister To Reply
  4. 04-11-2011, 11:36 AM #4
    markDuffy
    markDuffy is offline
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2021
    Posts
    318

    Re: Excel 2007 How to move trailing numbers into the adjacent column

    sorry it has lost my spaces.
    Reply With QuoteRegister To Reply
  5. 04-11-2011, 11:37 AM #5
    markDuffy
    markDuffy is offline
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2021
    Posts
    318

    Re: Excel 2007 How to move trailing numbers into the adjacent column

    Kangaroo 935
    Kangaroo Express 926
    Rite Aid Store 11360
    Short Stop 68
    Fairway Exxon
    "Value"
    Reply With QuoteRegister To Reply
  6. 04-11-2011, 11:37 AM #6
    markDuffy
    markDuffy is offline
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2021
    Posts
    318

    Re: Excel 2007 How to move trailing numbers into the adjacent column

    Please the last has the different columns and I am not sure how to get it here
    Reply With QuoteRegister To Reply
  7. 04-11-2011, 11:55 AM #7
    markDuffy
    markDuffy is offline
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2021
    Posts
    318

    Re: Excel 2007 How to move trailing numbers into the adjacent column

    Hi Thanks for all this it is excellent.
    I realize that my notes for you were not accurate enough. I did not include those which did not have a number or that only had one word. I have sorted these out by using the dodgy ones from the original. Just in case there is an easier way for the next one I have .



    What I got.

    Which was fantastic as my column was 37,000.

    Thanks again.

    Mark
    Reply With QuoteRegister To Reply
  8. 04-11-2011, 12:04 PM #8
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,099

    Re: Excel 2007 How to move trailing numbers into the adjacent column

    This will sort out the ones where there is no space and, by implication, no trailing number:

    =IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=0,"",SUBSTITUTE(A1," ",",",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))

    Regards
    Reply With QuoteRegister To Reply
  9. 04-11-2011, 12:12 PM #9
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,099

    Re: Excel 2007 How to move trailing numbers into the adjacent column

    OK, this will address all the variations you have described so far:

    =IF(NOT(ISNUMBER(--RIGHT(IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=0,"",SUBSTITUTE(A1," ",",",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),LEN(IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=0,"",SUBSTITUTE(A1," ",",",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))-FIND(",",IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=0,"",SUBSTITUTE(A1," ",",",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))))),A1,IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=0,"",SUBSTITUTE(A1," ",",",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))


    I suspect there may be a shorter version but I'll leave that as an open challenge.

    Regards

    Edit: it may not work if there are commas in the text part.
    Last edited by TMS; 04-11-2011 at 12:15 PM.
    Reply With QuoteRegister To Reply
  10. 04-14-2011, 09:27 AM #10
    markDuffy
    markDuffy is offline
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2021
    Posts
    318

    Re: Excel 2007 How to move trailing numbers into the adjacent column

    Thanks to you all again. I have so much to learn but it's excellent here.
    Reply With QuoteRegister To Reply
  11. 04-14-2011, 09:36 AM #11
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,099

    Re: Excel 2007 How to move trailing numbers into the adjacent column

    You're welcome.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1