adding a space and text to words in excel cells...

[marccohen]

Hi guys - I have a list of stock tickers between 3 and 5 letters... I need to amend these so each one has a 'space' followed by the letters 'US'

For example - the list would be ABB, BP, NGLOY but i need the cells to show ABB US, BP US, NGLOY US

Any help would be greatly appreciated

thanks

Marc

[#2]

@marccohen 

=REPLACE(SUBSTITUTE(A1,","," US,"),LEN(SUBSTITUTE(A1,","," US,"))+1,3," US")

Does this formula return the intended result?

[#3]

@marccohen 

This solution is specific to Excel 365 and moves the calculation to a Lambda function.  That does have a potential advantage that the same function may be applied to several lists if required.

"Worksheet formula"
= BYROW(ticker, CSV.USλ)

"CSV.USλ"
= TEXTJOIN(",", , TEXTSPLIT(v, ",") & " US")

[#4]

@marccohen 

If the goal is to add the "US" for show and not actually store that text in the cell, you could use custom cell formatting:

;;;General "US"