Forum Discussion

marccohen's avatar
marccohen
Copper Contributor
Jan 23, 2024

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

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

excel

3 Replies

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Jan 23, 2024

    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"

     

     

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jan 23, 2024

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

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jan 23, 2024

    marccohen 

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

     

    Does this formula return the intended result?

     

Resources