Find and replace a specific character, and then all subsequent characters

[tthebert]

Hi all,

I am new to the forum, and have a question in regard to Find and Replace.

I am trying to take a character from a list of tickers (&) and delete it and all characters after it.

For example,

Ticker
BRK/A&
SPY&fi
GLD&fi
VWO&fi
MDLZ&f
EEM&fi

Notice how the "&" is in different areas of the ticker, and some contain "f" or "fi" afterwards.

So how would I delete the & and all subsequent characters?

Thanks

 

[Jonmo1]

Welcome to the board.

Try

=LEFT(A2,FIND("&",A2&"&")-1)

 

[tthebert]

That worked! Thank you very much!

 

[Jonmo1]

Glad to help, thanks for the feedback

 

[Rick Rothstein]

That worked! Thank you very much!

I see you went for a formula solution, but since you originally asked about Find and Replace (making me think you wanted to delete the characters from your original data), here is how to do it using Replace. Select your cells, press CNTRL+H to bring up the Replace dialog box, type &* (an ampersand followed by an asterisk) into the "Find what" field, leave the "Replace with" field empty, press the "Options>>" button and make sure the "Match entire cell contents" checkbox is not checked, then click the "Replace All" button.