+ Reply to Thread
Results 1 to 6 of 6

Question using LEFT, MID, or RIGHT function

  1. #1
    Registered User
    Join Date
    05-23-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    6

    Question using LEFT, MID, or RIGHT function

    The following are all in column A1-A3

    Alabama 150,000
    Alaska 28,909
    Arizona 211,904

    I have a really lost list. I am just trying to get it so the state will end up in 1 column and the number in the other column.

    I used =LEFT(A1,FIND(",",A1,1)-4) to find the state but I cant figure out how to extract the number into a different column.

    Thanks for the help

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Question using LEFT, MID, or RIGHT function

    Hi Ziggy4e

    try
    Please Login or Register  to view this content.
    Regards
    Alastair

  3. #3
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,960

    Re: Question using LEFT, MID, or RIGHT function

    A non formula alternative : Data -text to columns - Delimited - Next - Select space as delimiter - Finish

  4. #4
    Registered User
    Join Date
    05-23-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    6

    Re: Question using LEFT, MID, or RIGHT function

    Quote Originally Posted by aydeegee View Post
    Hi Ziggy4e

    try
    Please Login or Register  to view this content.
    Regards
    Alastair
    Thanks for the formula. It worked except for when I ran into States with spaces in them like New Hampshire and North Dakota...

  5. #5
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,960

    Re: Question using LEFT, MID, or RIGHT function

    This will extract the last part of any string, delimiter being a space
    Please Login or Register  to view this content.

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Question using LEFT, MID, or RIGHT function

    Try this...

    Enter this formula in C1:

    =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

    Enter this formula in B1:

    =TRIM(SUBSTITUTE(A1,C1,""))

    Select B1:C1 and copy down as needed.

    With the range of formulas still selected do:

    Right click>Copy
    Right click>Paste Special>Values>OK

    Inspect the results to make sure you got what you wanted.

    Delete column A

    Select the range of values in column B.

    Goto Data>Text to Columns
    Click Finish
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1