Excel - Parse Last 2 Words to New Columns for Stock Symbol and Price - Duel 140 - Episode 1789

1,113 views · Published 20 September 2013 · 11:27 · Indexed 29 October 2026

Channel: MrExcel.com · 2013 · Education

Watch on YouTube

Microsoft Excel Tutorial: Separate Stock Name from the Ticker Symbol and Price. Extract everything but the last two words.

Welcome back to another thrilling episode of the Dueling Excel Podcast! In this exciting episode, Bill Jelen from MrExcel and Mike Girvin from ExcelIsFun take on a challenging task: extracting stock name, symbol, and price from a single cell. It's a common problem in Excel, and we're here to show you two distinct solutions.

Bill starts by demonstrating how to solve this problem using VBA (Visual Basic for Applications). With his VBA code, Bill programmatically determines the length of the cell and extracts the required information step by step. It's a powerful solution for those comfortable with VBA, and it works like a charm.

Mike, on the other hand, attempts to solve the problem using Excel formulas. He tries Flash Fill and even builds a complex formula that employs SUBSTITUTE, COUNTIF, and other functions to isolate the stock symbol and price. It's a more formula-driven approach for those who prefer to stay within the realm of Excel functions.

Whether you're a VBA enthusiast or a formula aficionado, this episode has something for you. We explore two distinct methods to tackle this common data extraction challenge, giving you the tools you need to work with financial data effectively.

While both methods have their pros and cons, we're excited to showcase them to help you choose the one that suits your Excel skills and preferences. Join us for this informative episode and learn how to extract valuable information from your data.

If you found this episode helpful, be sure to like, share, and subscribe for more valuable Excel tips and tricks. Leave your comments below and let us know your thoughts or any specific Excel challenges you'd like us to address in future episodes.

Stay tuned for more exciting duels between Bill Jelen and Mike Girvin, helping you master Excel one episode at a time. Thanks for watching, and we'll see you in the next Dueling Excel Podcast!"

Table of Contents
(00:00) Dueling Excel: Get Last Two Words from a Cell
(00:29) Cell contains Price, Stock Symbol, and Stock Name
(00:58) Flash Fill Fails
(02:10) Using VBA to parse from right side of cell
(03:59) Testing the macro
(04:54) Flash Fill needs a consistent pattern
(06:11) Zeroes cause problems for Flash Fill
(06:53) Mike tries a formula
(07:08) Count the spaces
(08:02) Replace last space with carat
(08:34) Using REPLACE
(09:01) Repeat for Stock Symbol
(10:48) Bill likes more examples for Flash Fill
(11:01) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelduel #excelvba #excelformula #excelflashfill

This video answers these common search terms:
excel how to parse text in cell
how parse cell in excel
how to parse a string of text in excel
how to parse data in excel using formula
how to parse stings in excel
how to count a specific word in a cell in excell
how to count cell words in excel
how to count for words in excel
how to count number of words in a cell on excel
how to count the number of words in a cell in excel
how to split excel words
how to split word in excel using formula
how to split words in a cell into columns excel

One cell has a security name, ticker symbol, then price. The complicated part is that the security name might have 3, 4, or 5 words. How can you extract the last 2 words into new cells and everything else as the security name?  Bill and Mike try Flash Fill, a VBA function, and a long formula to solve the problem.

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/1153610/

More from this channel