How to split full names into first and last names in Power Query
How-to · By Tahir Nas · Examples run in our simulator · Updated
9 min read
To split full names in Power Query, use Split Column > By Delimiter with a space and choose Right-most delimiter, so the last word becomes the surname and everything before it (first and any middle names) stays together. For anything beyond simple two-word names, add the columns yourself with Text.BeforeDelimiter and Text.AfterDelimiter, after tidying the spaces, so you can handle middle names, single names and "Last, First" order with explicit rules.
Left-most or right-most: the choice that matters
The Split Column dialog offers three places to split: the left-most delimiter, the right-most delimiter, or each occurrence (Split columns by delimiter, Microsoft Learn). For two-word names they all give the same result. For names with a middle name they don't.
Splitting at the left-most space keeps the middle name with the surname:
let
Source = #table({"Name"}, {{"Ada Lovelace"}, {"Grace Brewster Hopper"}, {"Cher"}}),
Split = Table.SplitColumn(Source, "Name", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, false), {"First", "Last"})
in
SplitResult
| First | Last |
|---|---|
| Ada | Lovelace |
| Grace | Brewster Hopper |
| Cher | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Grace's last name comes out as "Brewster Hopper". Cher has no space, so her Last is null.
Splitting at the right-most space keeps the middle name with the first name. The only change is the third argument of Splitter.SplitTextByEachDelimiter, startAtEnd, set to true (Splitter.SplitTextByEachDelimiter, Microsoft Learn):
let
Source = #table({"Name"}, {{"Ada Lovelace"}, {"Grace Brewster Hopper"}, {"Cher"}}),
Split = Table.SplitColumn(Source, "Name", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, true), {"First", "Last"})
in
SplitResult
| First | Last |
|---|---|
| Ada | Lovelace |
| Grace Brewster | Hopper |
| Cher | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Now First is "Grace Brewster" and Last is "Hopper". Which one you want depends on the report. A mailing list that greets people by first name wants the left-most split for the first name. A list sorted by surname wants the right-most split for the surname. You can have both by adding two columns instead of splitting one, which the next section does.
Note that Table.SplitColumn removes the original column. For names, that is rarely what you want, because you'll need the original to check the rules against.
A name-splitting query that handles the common cases
Real name columns contain more than tidy two-word names. This query handles extra spaces, "Last, First" order, a leading title and middle names, and adds First, Last and Middle columns.
let
Source = #table({"Name"}, {
{"Ada Lovelace"}, {"Grace Brewster Hopper"}, {"Cher"}, {" alan turing "},
{"Hopper, Grace"}, {"Dr Mary Somerville"}, {null}
}),
Tidy = Table.TransformColumns(Source, {{"Name", each if _ = null then null else Text.Combine(List.Select(Text.Split(Text.Trim(_), " "), each _ <> ""), " "), type nullable text}}),
Flipped = Table.TransformColumns(Tidy, {{"Name", each if _ <> null and Text.Contains(_, ",") then Text.Trim(Text.AfterDelimiter(_, ",")) & " " & Text.Trim(Text.BeforeDelimiter(_, ",")) else _, type nullable text}}),
Titles = {"Mr", "Mrs", "Ms", "Miss", "Dr", "Prof"},
NoTitle = Table.TransformColumns(Flipped, {{"Name", each if _ <> null and List.Contains(Titles, Text.BeforeDelimiter(_, " "), Comparer.OrdinalIgnoreCase) and Text.Contains(_, " ") then Text.AfterDelimiter(_, " ") else _, type nullable text}}),
First = Table.AddColumn(NoTitle, "First", each Text.BeforeDelimiter([Name], " "), type nullable text),
Last = Table.AddColumn(First, "Last", each if [Name] <> null and Text.Contains([Name], " ") then Text.AfterDelimiter([Name], " ", {0, RelativePosition.FromEnd}) else null, type nullable text),
Middle = Table.AddColumn(Last, "Middle", each if [Name] <> null and List.Count(Text.Split([Name], " ")) > 2 then Text.Combine(List.RemoveLastN(List.Skip(Text.Split([Name], " "), 1), 1), " ") else null, type nullable text)
in
MiddleResult
| Name | First | Last | Middle |
|---|---|---|---|
| Ada Lovelace | Ada | Lovelace | null |
| Grace Brewster Hopper | Grace | Hopper | Brewster |
| Cher | Cher | null | null |
| alan turing | alan | turing | null |
| Grace Hopper | Grace | Hopper | null |
| Mary Somerville | Mary | Somerville | null |
| null | null | null | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The output:
| Name (after tidying) | First | Last | Middle |
|---|---|---|---|
| Ada Lovelace | Ada | Lovelace | null |
| Grace Brewster Hopper | Grace | Hopper | Brewster |
| Cher | Cher | null | null |
| alan turing | alan | turing | null |
| Grace Hopper | Grace | Hopper | null |
| Mary Somerville | Mary | Somerville | null |
| null | null | null | null |
This example overwrites the Name column to keep it short. In your own query, do the tidying in a new column and leave the original untouched.
Tidy the spaces first
Text.Trim removes whitespace at the ends but not inside, so "alan turing" would still contain three spaces. Splitting it on single spaces would give empty pieces between the words. The Tidy step splits on spaces, drops the empty pieces and joins the rest with one space. The lesson on trimming, cleaning and capitalising text covers why Text.Trim alone isn't enough. Splitting on " " only splits on the ordinary space character, so names pasted from web pages may need non-breaking spaces replaced first.
The case of "alan turing" is left as it is. If you want "Alan Turing", add Text.Proper, but be aware it also turns "McDonald" into "Mcdonald", so apply it only where the source is clearly wrong.
Reverse "Last, First" names
Some systems export names as "Hopper, Grace". The Flipped step checks for a comma and, when there is one, puts the part after it in front. Run this before splitting on spaces, or "Hopper," becomes a first name.
Remove titles with a list
A title such as "Dr" would otherwise become the first name. The Titles list holds the titles you expect, and the NoTitle step removes the first word only if it is in that list, compared without case, because Power Query otherwise treats "dr" and "Dr" as different values (see case sensitivity and missing matches). A list is easy to extend when a new title turns up, and it only removes words you have listed. The Text.Contains(_, " ") test stops a name that is only a title from becoming empty.
First, last and middle names
- First is
Text.BeforeDelimiter([Name], " "). When there is no space, it returns the whole name, which is why Cher's First is "Cher". - Last uses
Text.AfterDelimiterwith the index{0, RelativePosition.FromEnd}, which means "the first delimiter counting from the end" (Text.AfterDelimiter, Microsoft Learn). The test for a space keeps single names from repeating as both first and last name. - Middle takes the words between the first and the last, and is null for two-word names.
Both delimiter functions accept a nullable text value (Text.BeforeDelimiter, Microsoft Learn), and the null row above comes through as null in every column rather than as an error. If you prefer the ribbon, Add Column > Extract > Text Before Delimiter and Text After Delimiter write the same functions, and their advanced options let you scan for the delimiter from the end. The site's Text.BeforeDelimiter and Text.AfterDelimiter reference pages have more tested examples.
Surname particles and suffixes
"Take the last word" gets some names wrong. For "Ludwig van Beethoven" it gives "Beethoven", where many lists expect "van Beethoven". For "Martin Luther King Jr" it gives "Jr". Two short lists fix the common cases:
let
Source = #table({"Name"}, {{"Ludwig van Beethoven"}, {"Oscar de la Hoya"}, {"Martin Luther King Jr"}, {"Grace Brewster Hopper"}, {"Cher"}}),
Particles = {"van", "von", "de", "der", "la", "le", "du", "da", "di"},
Suffixes = {"Jr", "Sr", "II", "III"},
Parts = Table.AddColumn(Source, "Parts", each
let
Words = Text.Split([Name], " "),
HasSuffix = List.Count(Words) > 2 and List.Contains(Suffixes, List.Last(Words), Comparer.OrdinalIgnoreCase),
Core = if HasSuffix then List.RemoveLastN(Words, 1) else Words,
Rest = List.Skip(Core, 1),
FirstParticle = List.PositionOf(List.Transform(Rest, each List.Contains(Particles, Text.Lower(_))), true),
LastWords = if List.IsEmpty(Rest) then {} else if FirstParticle >= 0 then List.Skip(Rest, FirstParticle) else {List.Last(Core)}
in
[First = Core{0}, Last = if List.IsEmpty(LastWords) then null else Text.Combine(LastWords, " "), Suffix = if HasSuffix then List.Last(Words) else null]),
Out = Table.ExpandRecordColumn(Parts, "Parts", {"First", "Last", "Suffix"})
in
OutResult
| Name | First | Last | Suffix |
|---|---|---|---|
| Ludwig van Beethoven | Ludwig | van Beethoven | null |
| Oscar de la Hoya | Oscar | de la Hoya | null |
| Martin Luther King Jr | Martin | King | Jr |
| Grace Brewster Hopper | Grace | Hopper | null |
| Cher | Cher | null | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The surnames come out as "van Beethoven", "de la Hoya", "King" (with Suffix "Jr") and "Hopper", and Cher's Last is null. Run this rule on the tidied names from the previous query, because Text.Split expects text and a null name would cause an error. The rule is: drop a known suffix from the end, then start the surname at the first particle after the first name, or use the last word if there is no particle.
The function returns a record with three fields, and Table.ExpandRecordColumn turns them into columns in one step. That keeps the word list from being split three times. The lesson on records in depth explains the pattern.
Treat both lists as yours to maintain, and remove full stops before matching if your data writes "Dr." or "Jr.". "Da" and "Di" are particles in some names and given names in others, and a short list will always miss some cases.
When not to trust the split
Names don't follow one structure. Many people have two surnames, some write the family name first, and some have a single name. Any rule gives a wrong answer for someone. Three habits keep that from causing harm:
- Keep the original full name in the output, next to the parts.
- Flag names with more than two words with a column such as
List.Count(Text.Split([Name], " ")) > 2, so a person can check them. - Ask for separate fields at source. If the form or system can capture first and last names separately, that is better than any split.
If you only need a greeting, you may not need a split at all. A "Preferred name" field avoids the problem entirely.
Practise splitting names
The exercise split customer names into first and last name practises the two-column split, and clean names and build a full name goes the other way, joining parts with the right spacing (the post on building dynamic text with Text.Format compares the ways to join them). The lesson on splitting columns and extracting text by delimiter and the guide to split column recipes cover the other splitters. All exercises on Power Query Exercises run in the browser and compare your result with the expected table. Name splitting is often part of a wider contact clean-up, covered in how to clean a messy CRM export.
Frequently asked questions
Why does my split create a third column?
Because the name has more than one space and the split was set to Each occurrence of the delimiter. Choose the left-most or right-most delimiter instead, which always gives two columns, or tidy repeated spaces before splitting.
How do I split on the last space only?
Use Right-most delimiter in the dialog, or Text.AfterDelimiter([Name], " ", {0, RelativePosition.FromEnd}) in a custom column for the surname and Text.BeforeDelimiter([Name], " ", {0, RelativePosition.FromEnd}) for everything before it. Guard single-word names as the main query does, because neither function finds a space in them.
Can I split names into rows instead of columns?
Yes, the dialog's advanced options can split into rows. That suits lists of names in one cell, such as "Ada; Grace; Alan", rather than the parts of one person's name. The post on survey multi-select answers uses the same split into rows, with the cleaning built in.
Sources
- Split columns by delimiter, Microsoft Learn (learn.microsoft.com)
- Splitter.SplitTextByEachDelimiter, Microsoft Learn (learn.microsoft.com)
- Text.AfterDelimiter, Microsoft Learn (learn.microsoft.com)
- Text.BeforeDelimiter, Microsoft Learn (learn.microsoft.com)
About the author
Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.
Keep reading
- Clean survey data in Power Query: multi-select answers to rowsSplit multi-select survey answers into one row per option in Power Query, count respondents per option correctly, map spelling variants and pivot back to flags.
- Cleaning phone numbers and postcodes with Power QueryStandardise UK phone numbers and postcodes in Power Query: keep only digits, fix +44 and lost leading zeros, re-space postcodes and flag values that fail.
- Fuzzy matching in Power Query: standardise inconsistent namesStandardise inconsistent product names in Power Query: normalise text first, let fuzzy merge suggest matches, then keep checked matches in a mapping table.
Practise Power Query in the browser with graded exercises checked against fresh data.
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.