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;"

 

[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}),"")))

Hello Sir,
I want to add few countries South America, Sri Lanka, South Africa, New Zealand with full name in above amazing formula.
Example: 123 South America 456 Sri Lanka 711 South Africa 167 New Zealand
into below formate
South America: 123; Sri Lanka: 456; South Africa: 711; New Zealand: 167

I want to put this into above formula

Thanks you.. have a nice day.

 

[Scott Huish]

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

 

[ctanxcel]

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

Hello Sir

It gives me below results.
Example
1244 South Africa 568 New Zealand 2000 South America
Output results as below
South Africa: 1244; New Zealand: 68; South America: 000

Could you please correct it.

Highly appreciated your all efforts. Thanks.
Have a nice day

 

[Scott Huish]

=TEXTJOIN({": ","; "},,INDEX(TEXTSPLIT(TEXTJOIN(",",,TRIM(TEXTSPLIT(A1,SEQUENCE(10,,0),,TRUE)),LET(A,--TEXTSPLIT(A1," "),FILTER(A,ISNUMBER(A)))),","),TOROW(TRANSPOSE(SEQUENCE(2,3)))))

This doesn't contain the parts about substitute US for United States, etc. yet. If someone could help me get the Count for what the A let returned. I think I could finish this. I need to replace the 3 with this number.

 

[RoryA]

Since you're splitting to columns, COLUMNS(A) should give you the count.

 

[Scott Huish]

It didn't recognize the A value in that part of the formula.

 

[RoryA]

You could just move that LET part to the start of the formula so that it is accessible everywhere.

 

[Scott Huish]

I couldn't figure out how to do that. The LET is creating a second array. The count of either of the two arrays would work, so I just repeated it.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN({": ","; "},,INDEX(TEXTSPLIT(TEXTJOIN(",",,TRIM(TEXTSPLIT(A1,SEQUENCE(10,,0),,TRUE)),LET(A,--TEXTSPLIT(A1," "),FILTER(A,ISNUMBER(A)))),","),TOROW(TRANSPOSE(SEQUENCE(2,COUNT(LET(A,--TEXTSPLIT(A1," "),FILTER(A,ISNUMBER(A))))))))),"United States","US"),"United Kingdom","UK"),"United Arab Emirates","UAE")

 

[RoryA]

As an alternative:

=LET(countries,TRIM(TEXTSPLIT(A1,,SEQUENCE(10,,0),TRUE)),clean_countries,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(countries,"United States","US"),"United Kingdom","UK"),"United Arab Emirates","UAE"),numbers,TOCOL(--TEXTSPLIT(A1," "),2),TEXTJOIN("; ",TRUE,clean_countries&": "&numbers))

 

[ctanxcel]

I couldn't figure out how to do that. The LET is creating a second array. The count of either of the two arrays would work, so I just repeated it.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN({": ","; "},,INDEX(TEXTSPLIT(TEXTJOIN(",",,TRIM(TEXTSPLIT(A1,SEQUENCE(10,,0),,TRUE)),LET(A,--TEXTSPLIT(A1," "),FILTER(A,ISNUMBER(A)))),","),TOROW(TRANSPOSE(SEQUENCE(2,COUNT(LET(A,--TEXTSPLIT(A1," "),FILTER(A,ISNUMBER(A))))))))),"United States","US"),"United Kingdom","UK"),"United Arab Emirates","UAE")

Many thanks to you Sir. Its working perfectly.. thank you for all time and efforts..