[theMusicMan]

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

[The Villan]

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.

[theMusicMan]

Hi The Villan

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

John

[The Villan]

John
You are very welcome. Glad it worked

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