Figart Consulting’s Excel Nifty Tip #1.5
NIFTY TIP # 1.5: Splitting a Full Name into Two Separate Cells
Problem: You have inherited a mailing list with hundreds of names. You’ll need to be able to sort the list alphabetically and you notice that the individuals’ first and last names are together in the same cell. Your blood pressure rises as you think you might have to do a tedious cut and paste job on this silly list, and wonder if you have the time to do the job.
Solution:
No fear. You have the time, because you have this little Nifty Tip.
To retrieve the first name, use the LEFT and FIND functions as shown in the following formula:
=LEFT(A2,FIND(“ “,A2))
To retrieve the last name use the RIGHT, LEN and FIND functions as shown in the following formula:
=RIGHT(A2,LEN(A2) –FIND(“ “,A2))
Huzzah. Your boss thinks you’re a genius and you’ve saved the day.
Related posts:
- Figart Consulting’s Excel Nifty Tip #1
- Nifty Tip #2: Combining the VLOOKUP and MATCH formulas
- NIFTY TIP #5: Adding or Subtracting Amounts from Different Ranges
- Nifty Tip #4: Organizing the Data Table for Proper Use of the VLOOKUP Formula
- Nifty Tip #3: CONCATENATE
« Nifty Tip #2: Combining the VLOOKUP and MATCH formulas | Home | Figart Consulting’s Excel Nifty Tip #1 »
Leave a Comment