Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

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


(!)

theMusicMan's Avatar
theMusicMan theMusicMan is offline
Member with 747 posts.
THREAD STARTER
 
Join Date: Oct 2001
Location: Wales, UK
15-May-2008, 04:16 AM #1
Solved: Excel: extracting left characters up to the 1st comma in a cell
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

Last edited by theMusicMan; 15-May-2008 at 04:23 AM..
The Villan's Avatar
Member with 2,226 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
15-May-2008, 04:41 AM #2
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
File Type: xls texttocolumns.xls (14.0 KB, 1026 views)
theMusicMan's Avatar
theMusicMan theMusicMan is offline
Member with 747 posts.
THREAD STARTER
 
Join Date: Oct 2001
Location: Wales, UK
15-May-2008, 04:54 AM #3
Hi The Villan

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

John
The Villan's Avatar
Member with 2,226 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
15-May-2008, 06:50 AM #4
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
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


Similar Threads
Title Thread Starter Forum Replies Last Post
Extracting Contacts in Outlook Express 6 with Russian Characters dhazer Web & Email 1 22-Apr-2008 01:31 PM
Excel - Find and replace the right most comma MattMurphy Business Applications 2 10-Oct-2007 02:17 AM
Export as comma delimited (Excel to Wordpad) slyvone Business Applications 1 27-Apr-2006 10:29 AM
Appending Data in Excel with a comma impelus Business Applications 10 31-Mar-2006 12:24 PM
Max Characters in a Cell cchalmers Business Applications 1 08-Dec-2005 07:57 AM

WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑