How to split Microsoft Excel data from 1 column into 2/3/multiple columns - Example: Full Names

Опубликовано: 31 Март 2025
на канале: Too Long; Didn't Watch Tutorials

479

So often we inherit a Microsoft Excel dataset with something like a full name in one column, yet we need to create separate columns for first, middle, and last names. This tutorial will not only show you how to address this situation, but it will teach you a formula if certain information is inconsistent and may be sometimes missing (example: middle name could be missing for some people and not others).

Don't be daunted by the formulas; just copy and paste them into your own spreadsheet and work from there. Whenever possible, keep it simple and just use the data/text to columns method I mention early in the video, but at the same time, life isn't always easy! Therefore, here are the formulas below if you need them.

BASIC FORMULA METHOD
Get First Name: =LEFT(A2,SEARCH(" ",A2)-1)

Get Middle Name: =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1))

Get Last Name: =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))

COUNT NUMBER OF NAMES
=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1

ADVANCED FORMULA METHOD
Get First Name (Same as above): =LEFT(A2,SEARCH(" ",A2)-1)

Middle Name: =IF(E2=3,MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1)),"")

Last Name: =IF(E2=3,RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2)+1))),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)))