1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel: extracting left characters up to the 1st comma in a cell

Discussion in 'Business Applications' started by theMusicMan, May 15, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. theMusicMan

    theMusicMan Thread Starter

    Joined:
    Oct 7, 2001
    Messages:
    747
    Hi All

    Just wondering if anyone is able to help me with the code required to extract the left most characters in a text cell, up to and not including the instance of the 1st comma. Some data doesn't have a comma in the record, others (most) do.

    Examples are:

    Example 1
    LINCOLN, NE 68501

    ... and the resulting data I want to extract would be

    LINCOLN

    Example 2
    One Ingram Blvd

    ... resulting data should remain intact

    Is there an easy formula that will allow me to do this folks please? Thanks in advance, am up against it here.

    John
     
  2. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    Open the attachment, and save it, so that you can have a practice on this data, before you attempt doing it on your own.

    Select cells A1 to A8
    Select the menu choice Data
    Select the menu choice Text to Columns

    A dialgue box will open

    Select the option Delimited and click on Next

    In the step 2 of 3 dialogue box, make sure that the only option ticked is the Comma box and then click on Next

    In the step 3 of 3 dialogue box,you will notice that Excel has already split the data into 2 columns (in the Data preview area).

    The first column is highlighted
    Click on the second column so that is highlighted and then select the option "Do not import column (skip)"
    Notice that the heading changes to Skip column.

    You can do one of the following at this point.

    The first is to have Excel place the first column that has been split into cells A1 to A8, so that you are only left with the data you need.
    If this is what you want, just click on Finish. You will only have the data you want in column A

    The second option, is to tell excel to place the data into another column and leave column A intact. If this is wjhat you want, then where it says Destination, change the cell reference to say $D$1. You then click on Finish and the data that you want is placed in column D, with the original data in column A left intact.

    Hope that helps. If there is anything you do not understand, get back and let me know, and I will explain further.
     

    Attached Files:

  3. theMusicMan

    theMusicMan Thread Starter

    Joined:
    Oct 7, 2001
    Messages:
    747
    Hi The Villan

    WOW - that worked perfectly, absolutely perfectly. Thanks so very much.

    John
     
  4. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    John
    You are very welcome. Glad it worked (y):)

    Would you be kind enough to scroll to the top of this thread and click on the option "Thread Tools" can you then click on "Solved".

    Thanks
    Les
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/712364