Results 1 to 5 of 5

Separating a column by numbers vs. letters when numbers are at the end of the cells?

Threaded View

  1. #1
    Registered User
    Join Date
    03-26-2009
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    26

    Separating a column by numbers vs. letters when numbers are at the end of the cells?

    I'd like to separate a column based on numbers vs. letters, where the numbers are at the end of the cells. For instance the following lists some Super Bowl scores, and an example score:
    New York Giants 1000	New England Patriots -250
    Green Bay Packers 31	Pittsburgh Steelers 25
    New Orleans Saints 31	Indianapolis Colts 17
    Pittsburgh Steelers 27	Arizona Cardinals 23
    New York Giants 17	New England Patriots 14
    Indianapolis Colts 29	Chicago Bears 17
    Baltimore Ravens 34	New York Giants 7
    Here there are two columns, one with the winner and the other with the loser. I'd like to separate this into 4 columns - one with the winner's name, the second column with the winner's points, the third column with the loser's name, and the fourth column with the loser's points.

    The issues are that the winner/loser names have variable lengths and word counts (some have two words like "Baltimore Ravens", others have three like "New York Giants"), and also the points scored can be an arbitrary number of digits (1 digit, 2 digits, 3 digits, 4 digits, etc.) and in theory can also be negative. So I can't simply use the formula:
    =RIGHT(A1,2)
    To separate the numerical values, since they might be an arbitrary number of digits. Likewise I can't use the following formula to get the team name:
    =LEFT(A1,LEN(A1)-3)
    How can I accomplish this task of separating these two columns into four columns, where the numerical values have their own columns? The result I'd like to achieve is four columns that look like this:
    New York Giants		1000	New England Patriots	-250
    Green Bay Packers	31	Pittsburgh Steelers	25
    New Orleans Saints	31	Indianapolis Colts	17
    Pittsburgh Steelers	27	Arizona Cardinals	23
    New York Giants		17	New England Patriots	14
    Indianapolis Colts	29	Chicago Bears		17
    Baltimore Ravens	34	New York Giants		7
    Last edited by Skywalker; 02-04-2012 at 03:00 PM.

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1