cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
leyburn19
Level 10

Formatting Complex Surnames

Fore any one interetsed,  I looked everywhere to help me with formulas to fix complex surnames like McCarthy, McDonald, O'Brian, Smith-Jones or Smith Jones to show upper and lower correctly. In excel or similar it can be done in one formula, but in flow it became an intersting past time.  

 

The net result was the below:

 

1.  I extract the surname from the trigger and run the following condition.  Note I coverted all text to Uppercase to ensure case matching so toUpper(triggerbody()?[Surname'])

 

step 1.JPG

 

2. The created 7 Compose Actions based on yes:

 

Compose1:  gets length of Surname -  length(triggerBody()?['Surname'])

 

Compose2:  detemines the position of the Surname where changs is to be made:

if(contains(triggerBody()?['Surname'],''''),indexOf(triggerBody()?['Surname'],''''),if(and(contains(toLower(triggerBody()?['Surname']),'c'),lessorequals(indexOf(toLower(triggerBody()?['Surname']),'c'),3)),indexOf(toLower(triggerBody()?['Surname']),'c'),if(contains(triggerBody()?['Surname'],'-'),indexOf(triggerBody()?['Surname'],'-'),if(contains(triggerBody()?['Surname'],' '),indexOf(triggerBody()?['Surname'],' '),'0'))))
 
Compose3: Creates the left portion of the Surname:
concat(toUpper(substring(triggerBody()?['Surname'],0,1)),toLower(substring(triggerBody()?['Surname'],1,outputs('Compose_2'))))
 
Compose4: determins the number of charachters in the right portion of the surname:
sub(outputs('Compose'),add(outputs('Compose_2'),1))
 
Compose5: Creates the Right side of the surname:
concat(toUpper(substring(triggerBody()?'Surname'],add(outputs('Compose_2'),1),1)),toLower(substring(triggerBody()?'Surname'],add(outputs('Compose_2'),2),sub(outputs('Compose_4'),1))))
 
Compose6: gets the character where the change is required.  This is needed if the character is a space or a '-'
substring(triggerBody()?['Surname'],outputs('Compose_2'),0)
 
Compose7: Puts the word all back together:
concat(outputs('Compose_3'),outputs('Compose_6'),outputs('Compose_5'))
 
Somehow it works until I suddenly get a special name like de Angelo. Ugh! the complexity of teh English Language.
3 REPLIES 3
Community Support Team
Community Support Team

Re: Formatting Complex Surnames

Hi @leyburn19 ,

 

I have made a simple flow using the function you provided, it seems that nothing difference happens between the original name and the formatted name.

 

Such as McCarthy still be McCarthy after formatted, O’Brain stil be O’Brain after formatted.

1.PNG

 

Could you show me more details on how do you want to format the SurName?

 

Best regards,

Mabel

 

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
leyburn19
Level 10

Re: Formatting Complex Surnames

It is for checking and fixing,  Say O'Brian was o'brian or MacDonald was macdonald.  This fixes the Case to be proper.  If it is already correct it comes out with no changes.  users often do not do it properly

Community Support Team
Community Support Team

Re: Formatting Complex Surnames

Hi @leyburn19 ,

 

I suppose that you need to add a Condition to check if the first letter in the word is capital or not.

 

If yes, do the previous action. If no, do corresponding actions.

 

The function used in the Condition is:

 

first(toUpper(triggerBody()['text']))

is equal to

first(triggerBody()['text'])

 

1.PNG

 

Best regards,

Mabel

 

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 200 members 6,424 guests
Please welcome our newest community members: