Only Capitalize First Letter & Delete Last Name

[zulhfreelancer]

\1

Hi guys,

I need some help here. I have about 1000 subscribers name & email list here. I want to do some housekeeping where:

1. I want to remove 'last name' (in this case, Smith is the last name and I only want John to be there).

2. I want to only capitalize the first letter and did not want ALL CAPS records like JOHN SMITH.

Any Excel shortcuts to settle this?

Thank you.

[alansidman]

In columns B1. =PROPER(LEFT(A1,FIND(" ",A1,1)))

Copy down

Alan

[MarvinP]

Hi zulhfreelancer and welcome to the forum,

I'd suggest three functions to learn.
First is Find(" ",A1) which will find the first space in "JOHN SMITH"
Then Left(A1, Find(" ",A1) which will return only JOHN
Then Proper() or the above which will return John.

I'd do a TRIM function at the end to remove leading or trailing spaces too.

If JOHN SMITH was in A1 then my formula in B1 would be:

=TRIM(PROPER(LEFT(A1,FIND(" ",A1))))

[zulhfreelancer]

In columns B1. =PROPER(LEFT(A1,FIND(" ",A1,1)))

Copy down

Alan

Thank you very much!

[zulhfreelancer]

Hi zulhfreelancer and welcome to the forum,

I'd suggest three functions to learn.
First is Find(" ",A1) which will find the first space in "JOHN SMITH"
Then Left(A1, Find(" ",A1) which will return only JOHN
Then Proper() or the above which will return John.

I'd do a TRIM function at the end to remove leading or trailing spaces too.

If JOHN SMITH was in A1 then my formula in B1 would be:

=TRIM(PROPER(LEFT(A1,FIND(" ",A1))))

Thank you very much!

[zulhfreelancer]

Good day guys,

I've another question here that closely related to this thread.

If I have these records in an Excel file (http://i.imgur.com/o13Cv.png),
what could I do to trim them to obtain just their first name?

1. Remove text after space (Smith) and obtain only John.
2. Remove text after space (SMITH) and obtain only John and capitalize only the front letter.
3. Maintain records that already capitalize it's front letter and doesn't have last name.
4. Capitalize front letter for name that doesn't have last name.
5. Normalize the case and only capitalize the front letter for name that doesn't have last name.

Thank you.

[alansidman]

Try this:

=IF(ISERR(FIND(" ",A1,1)),TRIM(PROPER(A1)),TRIM(PROPER(LEFT(A1,FIND(" ",A1,1)))))

[zulhfreelancer]

Thank you Alan! It's worked for me.