Solved

Excel formula assistance - Need to split data in cells

Posted on 2011-03-14
3
191 Views
Last Modified: 2012-05-11
I have a column with the following info:

WASHINGTON, GEORGE M 034562           55195929
OBAMA, BARACK H 073476           55195937
BUSH, GEORGE 290334           55195945
KENNEDY, JOHN F 289573           55195952
NIXON, ROBERT 295735           55195960
DOE, JOHN O 234540          55195978
PACINO, AL BUNDY 176439           55195906
STARK IV, TONY W 067600           55195994
CAPONE, AL 024325           5519600D
and so on...

As you can see some of the names have commas, some have initials, some have periods INSTEAD of commas. All I need is a formula that can extract in COLUMN B, just the name WITHOUT the numbers at the end. So my result in COLUMN B should look like:

WASHINGTON, GEORGE M
OBAMA, BARACK H
BUSH, GEORGE
KENNEDY, JOHN F
NIXON, ROBERT
DOE, JOHN O
PACINO, AL BUNDY
STARK IV, TONY W
CAPONE, AL

Thanks so much, in advance!

-JJr.
0
Comment
Question by:bpatton_psr
3 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 63 total points
ID: 35132747
You could extract everything before the first digit, i.e. in B2

=TRIM(LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&1234567890))-1))

regards, barry
0
 
LVL 9

Assisted Solution

by:McOz
McOz earned 62 total points
ID: 35132766
It looks as if there are the same number of "number characters" after each name - if this is the case throughout, you could get by it simply by putting this formula in column B:

=TRIM(SUBSTITUTE(A1,RIGHT(A1,25),""))

Cheers
0
 

Author Closing Comment

by:bpatton_psr
ID: 35132803
Excellent, thanks!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now