How to use Text Function to keep the Acronyms in capital and rest of the words in small letters

[Anonymous]

Hi memebers, 

I have a situation, 

I have a list of company names, thats all mixed: 

Like this: 

1.  ABC DONAWAN 

2.  XYZ

3.  John Smith

the out put i want is:

1. ABC Donawan

2.  XYZ

3. John Smith 

How can I get this outcome? if I try "Capitalize each word" in M Query, then XYZ becomes Xyz, but XYZ is an acronym and i want it to stay capital. 

Can someone please help. 

Thanks in advance. 🙂 

regards

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

According to your needs, you can try to use the upper and lower functions. Their function is to convert the letters specified in Text into upper and lower case. I did a test. Refer to the following:

col_N =
UPPER ( LEFT ( [susanne], 1 ) )
    & LOWER ( RIGHT ( [susanne], LEN ( [susanne] ) - 1 ) )

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@Anonymous 

How do you identify which part is the acronym when they are mixed?

[Anonymous]

Hi, it just makes sense looking at the data. 
for example if a customer name is: AHI Roofing, then AHI is the acronym. Usually customer names have an acronym and then a word that makes sense. 

[Fowmy]

@Anonymous 

So if the 1st word is in all capitals then the rest should be converted to sentence case right?

[Anonymous]

hi, 

this is a good example: 

 Output:

Dixon & Haddon

Pegasus Industrial Engineering Ltd

E R Freeman Ltd - Corporate

Allwin Steel Enterprises

Hope that makes more sense. 

Regards

[amitchandak]

@Anonymous , Try like this , a new column in power query

List.First(Text.Split([column]," "))  &  " "  & Text.Proper(Text.Combine(List.LastN(Text.Split([column]," "),List.Count(Text.Split([Column])) -1) ," "))

or like

Text.Combine( {  

List.First(Text.Split([column]," "))  ," "  ,Text.Proper(Text.Combine(List.LastN(Text.Split([column]," "),List.Count(Text.Split([Column])) -1) ," "))

})

[amitchandak]

@Anonymous , what is the difference between data and output. Somehow I am not clear

[Anonymous]

My bad, 

I made a mistake and have now edited my original query. 

But here is what i want: 

Like this: 

1.  ABC DONAWAN 

2.  XYZ

3.  John Smith

the out put i want is:

1. ABC Donawan

2.  XYZ

3. John Smith 

Regards