Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Excel formula assistance - Need to split data in cells

Posted on 2011-03-14
Medium Priority
198 Views
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

-JJr.
0
Question by:bpatton_psr
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 50

Accepted Solution

barry houdini earned 252 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

McOz earned 248 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

ID: 35132803
Excellent, thanks!!
0

## Featured Post

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
###### Suggested Courses
Course of the Month9 days, 3 hours left to enroll