Jan 23 2024 05:08 AM
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
Jan 23 2024 05:40 AM
=REPLACE(SUBSTITUTE(A1,","," US,"),LEN(SUBSTITUTE(A1,","," US,"))+1,3," US")
Does this formula return the intended result?
Jan 23 2024 06:14 AM
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")
Jan 23 2024 09:26 AM
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"