Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Split 1st Name & Surname

1. I agreed to these rules
Join Date
7th December 2006
Posts
2

## Split 1st Name & Surname

Hi everyone,

I don't know Excel very well and I've got a huge list of people's names, but their first and last names both occupy a single cell. I'd like to arrange the list to have one cell for their first names and another for their surnames - is there a quick way to get Excel to do that? The list is incredibly long and I'd love to get around doing this manually... is there a formula-based solution? I am using Office 2000.

PS: I don't know what other detail to provide, if this is too vague, please let me know what else I can include.

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Single-cell Name & Surname Into 2 Cells

Use Data>Text to Columns... Or

=TRIM(LEFT(A1,FIND(" ",A1))) In B1
and
=TRIM(MID(A1,FIND(" ",A1),256)) in C1
Copy down via Fill Handle

3. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## Re: Single-cell Name & Surname Into 2 Cells

The formulas will give two new columns. Assuming the names start in cell A1, put this formula in cell B1 and fill down:

=LEFT(A1,FIND(" ",A1)-1)

and this in cell C1 and fill down:

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

There are other ways to do this.

Excel Video Tutorials / Excel Dashboards Reports

4. I agreed to these rules
Join Date
7th December 2006
Posts
2

## Re: Split 1st Name & Surname

Wow, you guys are amazing, that worked so well!! You have no idea how much time you have just saved me! THANK YOU!!!

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno