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

Copper Contributor

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



3 Replies


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


Does this formula return the intended result?

adding a space and text.png



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λ)

= TEXTJOIN(",", , TEXTSPLIT(v, ",") & " US")



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"