Need Formula for flip words

[ctanxcel]

I want to convert data into perticular formate. Below are the example
Examples:
Cell A1 "1232 india 445 United States 468 United Kingdom 96 United Arab Emirates"

Into this format

Cell A1 "India: 1232; US: 445; UK: 468; UAE: 96;"

 

[iggydarsa]

you can always use Find/Replace option (Ctrl+H)

 

[Scott Huish]

Except for the capitalizing of india which another SUBSTITUTE could be added if it's a regular occurrence:

=LET(a,TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"United States","US"),"United Kingdom","UK"),"United Arab Emirates","UAE")," "),TEXTJOIN({": ","; "},TRUE,IFERROR(INDEX(a,{2,1,4,3,6,5,8,7,10,9}),"")))

 

[Joe4]

How many different locations do you have?
Do you have some sort of table to show what each one needs to be shortened to?

Also, when you select your "Office Version" and "Platform" in your Profile, it is not very helpful to select every single one! Just pick the one you are currently using. There are lots of great functions available in the newer versions. The point of that is to let us know which version you are using, so we make sure we do not recommend something you cannot use.

 

[ctanxcel]

Except for the capitalizing of india which another SUBSTITUTE could be added if it's a regular occurrence:

=LET(a,TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"United States","US"),"United Kingdom","UK"),"United Arab Emirates","UAE")," "),TEXTJOIN({": ","; "},TRUE,IFERROR(INDEX(a,{2,1,4,3,6,5,8,7,10,9}),"")))

Wow... Its amazing..Its working perfect. Many thanks to you..

 

[ctanxcel]

you can always use Find/Replace option (Ctrl+H)

Oh yeah it's good idea thank you

 

[ctanxcel]

How many different locations do you have?
Do you have some sort of table to show what each one needs to be shortened to?

Also, when you select your "Office Version" and "Platform" in your Profile, it is not very helpful to select every single one! Just pick the one you are currently using. There are lots of great functions available in the newer versions. The point of that is to let us know which version you are using, so we make sure we do not recommend something you cannot use.

View attachment 117009

No I don't not have any other locations. And I don't have sort table to show. I am using Microsoft office 365 version for Excel.

 

[Joe4]

I am using Microsoft office 365 version for Excel.

OK, would you mind updating your profile (what I circled in my previous post) to reflect that?
That will be helpful to anyone trying to answer your questions in the future.

 

[ctanxcel]

OK, would you mind updating your profile (what I circled in my previous post) to reflect that?
That will be helpful to anyone trying to answer your questions in the future.

Yes. Its updated now. Thanks for reminding me. Have a nice day..

 

[Joe4]

Yes. Its updated now. Thanks for reminding me. Have a nice day..

Excellent!
Thank you very much!