Splitting First/Last Name and a little more

0

I have a field with names configured this way:

AALIYAH JACKSON (FY90)

I want to end up with them looking like this in a first name field and Last name field.

FIrstName. LastName

Aaliyah        Jackson

How do I strip the (fy90) out and elimante the caps?

Thanks

Answer
Discuss

Answers

0
Selected Answer

Remove Caps

=PROPER(A1)

or

=LOWER(A1)

Remove Parentheses Section

  1. Select desired cells.
  2. Hit Ctrl + H to go to the Find/Replace window
  3. In Find what: type:
    1. (*)
  4. Hit Replace All

Be careful with this! Backup your data first as it will remove ALL sections that are surrounded by parentheses!

Split the Names

  1. Select the column with the names.
  2. Data tab > click Text to Columns
  3. Select Delmited and hit Next
  4. Check next to Space and remove all other check marks.
  5. Hit Finish

Make sure there is no data to the right of the name column before you do this!

Split Names with Formulas

First name:

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

Last name:

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

These formulas assume that the separator for the names is a space and it will get the first name and then whatever is after the first name.

That shold do it :)

Discuss

Discussion

I'm doing ok but not sure how to handle names like this:
EDWARD HENDERSON JR (L803)

How do keep "jr" with Henderson?
seamus (rep: 4) Apr 5, '19 at 4:41 pm
I'd like to have a First Name and Last Name field only but not sure how to keep the "Jr." in the example above or when a person has 2 last names together.
seamus (rep: 4) Apr 8, '19 at 11:17 am
Answer updated! Sorry for the wait!
don (rep: 1665) Apr 9, '19 at 9:36 am
Add to Discussion
0

I don't know much about excel so I would use "text to columns" to split on space.

NowNypull have 4 columns.

Original.

First name.

Last name.

Column with  (FY90). Delete it.

Use Lower function per above.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login