Need to add countries in formula

[ctanxcel]

Want to add countries in below substitute Formula formula with full names.
South America Sri Lanka South Africa New Zealand

South Africa sould be South Africa and same for others.

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

Thanks

 

[RoryA]

South Africa sould be South Africa

I don't understand: that is not a substitution.

 

[ctanxcel]

I don't understand: that is not a substitution.

For example.
SUBSTITUTE(SUBSTITUTE(A1,"South Africa","South Africa"),"South America","South America")

 

[RoryA]

That makes no sense. Why would you substitute something with itself? That doesn't achieve anything.

 

[ctanxcel]

That makes no sense. Why would you substitute something with itself? That doesn't achieve anything.

I am using in my daily task. Let it be. Thanks. Have nice day.

 

[RoryA]

I think you missed my point. Using:
=SUBSTITUTE(A1,"South Africa","South Africa")
achieves nothing more than just using:
=A1

 

[Scott Huish]

It has to do with my original solution, but the needs changed slightly. He's posting multiple posts for the same question.

ctanxcel You need to continue on in your original thread, not make new posts for the same question.​

 

[ctanxcel]

It has to do with my original solution, but the needs changed slightly. He's posting multiple posts for the same question.

ctanxcel You need to continue on in your original thread, not make new posts for the same question.​

Yes I already posted but there are no solutions that's why I am looking for solutions via another post. Have a nice day

 

[ctanxcel]

I think you missed my point. Using:
=SUBSTITUTE(A1,"South Africa","South Africa")
achieves nothing more than just using:
=A1

Example I cell A1: 123 South America 456 Sri Lanka 711 South Africa 167 New Zealand
into below formate in cell b1
South America: 123; Sri Lanka: 456; South Africa: 711; New Zealand: 167

This is just for your information what actually I am doing..

 

[Scott Huish]

Yes I already posted but there are no solutions that's why I am looking for solutions via another post. Have a nice day

Yes, but you're not supposed to. You just reply to your original post and it bumps it back up to the top. I saw a different post of this and thought you had an answer. It's very confusing for people to try and help you when there are multiple posts for the same thing. I'm very close. I might need help on one thing.