Extract Leftmost and Rightmost Names using PQ M Functions

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to use the Power Query M Function to extract First and Last Name from Full Name. Let’s get started

In the caption below, I have simple data in my Power Query. I want to extract the leftmost names i.e Phoebe, Tina Abiola etc to be my First name and the rightmost names i.r Harding, John, David etc to be my Last Name

First Name Extraction

To extract the first name, we are going to deploy the Text.Start and Text.PositionOf Power Query M Functions

• Text.Start, which is the same as LEFT function in Excel returns the first characters of text as text value.
• Text.Start Syntax                                              =Text.Start(text, count)
• Text.PositionOf, which is the same as FIND function in Excel, returns the position of the specified occurrence of the text value substring found in text
• Text.PositionOf Syntax                                  =Text.PositionOf(text, substring, occurrence [optional])
• In the General group of the Add Column tab, click on Custom Column
• In the Custom Column dialogue box, execute the following: First Name
• In the Custom Column formula, execute:
• Text.Start([Account Manager],Text.PositionOf([Account Manager],” “)).

What the formula implies is that we want to extract the leftmost name in the Account Manager column. Since, each of the names do not have the exact length of characters, we used Text.PositionOf to find the position of the Space between each name in the Account Manager column

• Click OK

From the caption above, we have the first name properly extracted without any extra spaces as verified using Text.Length function in the First Name Text Length

Last Name Extraction

Extracting the Last Name is quite more technical than the first name. To extract the Last Name, we are going to deploy the Text.End, Text.Length and and Text.PositionOf Power Query M Functions respectively.

• Text.End, which is the same as RIGHT function in Excel returns the last characters of text as text value.
• Text.End Syntax                                =Text.End(text, count)
• Text.Length, which is the same as LEN function in Excel, return the number of characters in the text
• Text.Length Syntax                         =Text.Length(text)
• Text.PositionOf, which is the same as FIND function in Excel, returns the position of the specified occurrence of the text value substring found in text
• Text.PositionOf Syntax                                  =Text.PositionOf(text, substring, occurrence [optional])
• In the General group of the Add Column tab, click on Custom Column
• In the Custom Column dialogue box, execute the following: Last Name
• In the Custom Column formula, execute:

Text.End([Account Manager],Text.Length([Account Manager])-Text.PositionOf([Account Manager],” “)-1)

The above M code implies that we want to extract the rightmost characters in Account Manager column. Since, the rightmost characters do not have the same length and there is a space character between the First and Last Name. Hence, we deployed Text.Length to verify the number of characters as the second argument of the Text.End. Also, we deployed Text.PositionOf to find the space and used minus 1 to handle the space which is a character on its own.

• Click OK

From the caption above, we have the Last Name properly extracted without any extra spaces as verified using Text.Length function in the Last Name Text Length