Solved

Excel formula assistance - Need to split data in cells

Posted on 2011-03-14
3
192 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

773 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