Reverse Switch Text by Delimitator

[sslattery17]

A few of our clients send reports with last name, first name and I have used the below formula in the past and I used switch to achieve the same result but can't get it to work.  What part of the syntax am I getting wrong or missing?? Attached sample data and screenshot

with Power Q:

Text.Combine(List.Reverse(Text.Split([Resident],",")), " ")  

Using below and creating new column: 

SWITCH([Resident],",")), " ")

Thank you in advance for the assistance.  SS

[atcodedog05]

Hi @sslattery17 

Try Rexgex formula like this

REGEX_Replace([Resident], "(.*), (.*)", "$2 $1")

[atcodedog05 · Accepted solution]

Hi @sslattery17 

Here is a snapshot

Hope this helps : )

[sslattery17]

@atcodedog05  Thank you for the quick response and help.  This solved the issue.  

[CoG · Accepted solution]

Similarly,

If Regex is not your strong suit, you can make use of the oft forgotten GetPart() function in the Formula Tool:

TRIM(TRIM(GetPart([Name], ",", 1)) + " " + TRIM(GetPart([Name], ",", 0)))

Hope this helps and Happy Solving!

[atcodedog05]

Happy to help : ) @sslattery17 

Cheers and have a nice day!

[atcodedog05]

Hi @CoG 

I agree with you on often forgotten part 😅 and also agree with you that its really powerful 😎

[sslattery17]

@CoG thank you.  I like the trim option. 

Sometimes the data I bring in has a combination of last name, first name and a second tenant so last name, first name last name, first name.  Sometimes if it is just a simple two tenant without last name first; then I can text to column by delimitator but when they are reserved this option makes it difficult. Any suggestions for two tenant reverse case?

[atcodedog05]

---

@sslattery17 wrote:

@CoG thank you.  I like the trim option. 

Sometimes the data I bring in has a combination of last name, first name and a second tenant so last name, first name last name, first name.  Sometimes if it is just a simple two tenant without last name first; then I can text to column by delimitator but when they are reserved this option makes it difficult. Any suggestions for two tenant reverse case?

---

Hi @sslattery17 ,

Can you provide sample data for this? Does tenant names have a different separator?

[CoG]

Can you share some sample data for all of the different cases that may exist for this more complex case, regarding how multiple tenants can be combined? This would help make sure any help is comprehensive for you. Depending on the variability, Regex Tool: Tokenize may be a better option for you compared to the Text to Column tool.