Today we are going to discuss about numerous methods in Excel and Power Query to reverse the names with multiple words. we already discussed a lot about reversing First Name and Last Name in below posts.
How to reverse First Name and Last Name - Part 1:
How to reverse First Name and Last Name - Part 2:
But this post is about reversing more than 2 names.
Method-1:
=MAP(B3:B9,LAMBDA(x,REDUCE("",TEXTSPLIT(x," "),LAMBDA(a,v,v&" "&a))))
MAP and REDUCE functions are LAMBDA helper functions which will help us to iterate in an array and do custom functions.
REDUCE function is programmed to deliver the final result after all iterations by evaluating custom functions. But MAP function will deliver us each result.
Arguments of MAP function are,
=MAP(array,lambda_or_array2,...)
Variable 'x' in LAMBDA function of MAP holds the values from B3:B9 during iterations.
In the first iteration variable 'x' holds B3 cell value,
In the 2nd iteration variable 'x' holds B4 cell value and so on.
in each iteration our customized function uses REDUCE function to accomplish our task.
Arguments of REDUCE function are,
=REDUCE(initial_value,array,function)
During the first iteration of MAP function,
x -> Chowdhury Hasan Tanver
in REDUCE function,
initial_value -> ""
array -> TEXTSPLIT(x," "), which will deliver us {"Chowdhury","Hasan","Tanver"}
function -> contains 2 variables
a -> accumulator or state,
v -> current value,
In the first iteration of REDUCE function,
v&" "&a
v -> "Chowdhury"
a -> ""
if we concatenate current value and accumulator with delimiter as SPACE character,
then the result is -> "Chowdhury"
In the 2nd iteration of REDUCE function,
v&" "&a
v -> "Hasan"
a -> "Chowdhury"
if we concatenate current value and accumulator with delimiter as SPACE character,
then the result is -> "Hasan Chowdhury"
In the 3rd iteration of REDUCE function,
v&" "&a
v -> "Tanver"
a -> "Hasan Chowdhury"
if we concatenate current value and accumulator with delimiter as SPACE character,
then the result is -> "Tanver Hasan Chowdhury"
Here, REDUCE function will deliver us the final result which is "Tanver Hasan Chowdhury".
MAP function will help us to carry this custom function across each cell in array.
Method-2:
=LET(nm,B3:B9,s,SUBSTITUTE,mx,MAX(LEN(nm)-LEN(s(nm," ",))+1),TRIM(BYROW(MID(s(nm," ",REPT(" ",50)),50*(mx-SEQUENCE(,mx))+1,50)&" ",CONCAT)))
here,
variable 'nm' contains the range B3:B9,
's' contains SUBSTITUTE function, from now on instead of using whole function name, we can call it with name 's'.
'mx' counts the number of words in array 'nm' and picks the maximum value of count,
in our case it is 4,
BYROW is the LAMBDA helper function, which will carry our custom function row by row in given array.
In the first iteration of BYROW function,
variable 'nm' holds -> "Chowdhury Hasan Tanver"
using SUBSTITUTE function, we replace the spaces in name with spaces of 50 characters,
output will look like below,
="Chowdhury Hasan Tanver"
From this text, MID function will help us to pick each name as shown below,
text -> name with 50 space character,
start num -> 50*(4-SEQUENCE(,4))+1, result of this is {151,101,51,1}
num_chars -> i.e number of characters are 50.
Output of MID function is,
{" "," Tanver "," Hasan ","Chowdhury "}
This is the first element of array for our BYROW function and the custom function which we ask it to do is CONCAT (ETA LAMBDA requires no variable and LAMBDA function).
Result of first iteration is,
"Tanver Hasan Chowdhury"
TRIM function helps us to remove leading and trailing spaces and to provide our expected result. This will continue till the last element in 'nm' variable which is B9 cell value.
Method-3:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Result = Table.FromList(List.Transform(Source[Wrong Name], (f) =>
Text.Combine(List.Reverse(Text.Split(f, " ")), " ")), null,{"Correct Name"})
in
Result
We create a list from Source[Wrong Name],
then we can use List. Transform function to transform each element in list.
First element in list is "Chowdhury Hasan Tanver" and our transformation involves,
Text. Split -> which will split the text based on the delimiter " " and will deliver us the list.
{"Chowdhury","Hasan","Tanver"},
List. Reverse function will reverse the list as shown below,
{"Tanver","Hasan","Chowdhury"}
Text. Combine function will combine these lists of texts with delimiter " ".
Result of this function is,
"Tanver Hasan Chowdhury"
These transformations will be carried out for each element in the list of Wrong Name.
Table. FromList function will convert the list into table.
Method-4:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Result = Table.FromColumns({List.Transform(Source[Wrong Name], (f) =>
List.Accumulate(Text.Split(f, " "), "", (s,c) => c &" "& s))}, {"Correct Names"})
in
Result
This is the Power Query version of our first method in Excel formulas.
Comments