Splitting and merging columns in Power BI - Cell-by-Cell Formula Trace

Sample Data

This table shows a list of full names in column A. We want to split these full names into first and last names in columns B and C, then merge them back with a comma separator in column D.

Cell	Value
A1	Full Name
A2	John Doe
A3	Jane Smith
A4	Alice Johnson
B1	First Name
C1	Last Name
D1	Merged Name

Formula Trace

First Name = LEFT([Full Name], FIND(" ", [Full Name]) - 1) Last Name = RIGHT([Full Name], LEN([Full Name]) - FIND(" ", [Full Name])) Merged Name = [Last Name] & ", " & [First Name]

Step 1: FIND(" ", "John Doe")

Step 2: LEFT("John Doe", 5 - 1)

Step 3: LEN("John Doe")

Step 4: RIGHT("John Doe", 8 - 5)

Step 5: "Doe" & ", " & "John"

Cell Reference Map

    A          B           C           D
1 | Full Name | First Name | Last Name | Merged Name
2 | John Doe |            |           |           
3 | Jane Smith|           |           |           
4 | Alice Johnson|         |           |           

Arrows:
A2 -> B2 (split first name)
A2 -> C2 (split last name)
B2, C2 -> D2 (merge names)

Column A contains the original full names. Columns B and C are created by splitting the full name at the space. Column D merges the split names with a comma separator.

Result

    A           B          C          D
1 | Full Name  | First Name| Last Name | Merged Name
2 | John Doe   | John      | Doe       | Doe, John
3 | Jane Smith | Jane      | Smith     | Smith, Jane
4 | Alice Johnson| Alice   | Johnson   | Johnson, Alice

The final table shows the original full names split into first and last names, then merged back in 'Last Name, First Name' format.

Sheet Trace Quiz - 3 Questions

What does the FIND function return when applied to 'John Doe' looking for a space?

A 4

B 6

C 5

D 0

Key Result

Split full name by finding space position; LEFT extracts first name; RIGHT extracts last name; merge with & and separator.