Excel - Master Math in Text with VBA Macro & Excel - Dueling Excel - Episode 1770
1,399 views · Published 9 August 2013 · 7:47 · Indexed 30 October 2026
Channel: MrExcel.com · 2013 · Education
Microsoft Excel Tutorial - Master Math in Text with VBA Macro & Excel - Dueling Excel. Welcome back to another exciting episode of the Dueling Excel podcast! In this episode, Bill Jelen from MrExcel and Mike Girvin from Excel Is Fun will be tackling a challenging question from YouTube. The question involves generating a result from a text that contains mathematical expressions and units. This is episode 135, titled "Do the Math in the Text". As you can see, the text contains various units and symbols, making it difficult to calculate the desired result. Bill's solution involves using a VBA macro. He starts by selecting the cells that need to be converted and making sure they are completely empty. Then, he inserts a module and creates a macro called "Foo". This macro uses a loop to go through each character in the text and extract the necessary numbers and symbols. The result is then written in a separate cell, allowing for easy calculation and manipulation. But wait, Mike has a different approach! He uses the SUBSTITUTE function to remove any unwanted letters and symbols from the text. Then, he uses the SUBSTITUTE function again to replace the "X" with an asterisk, making it a valid mathematical expression. But how does he calculate the result? Mike takes us back in time to the Excel for Macro functions. He shows us a neat trick where we can test these functions on a macro sheet, making it easier to use and understand. He also introduces us to the concept of a universal relative cell reference, which allows for easy calculation no matter where the defined name is placed. And that's not all! Mike also shares a useful keyboard shortcut for inserting a new sheet and a handy toggle for viewing formulas and results. With these tips and tricks, you'll be able to tackle any mathematical expression in a text with ease. We hope you enjoyed this episode and learned something new. Don't forget to check out Excel Is Fun's channel for more videos on Excel for Macro functions. Thanks for watching and we'll see you in the next Dueling Excel podcast! Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) Do the Math in the Text of a Cell (00:20) Solution using VBA macro (01:35) Explanation of the VBA code (02:00) Alternative solution using Excel for Macro functions (03:00) Explanation of the Excel for Macro function trick (05:28) Creating a universal relative cell reference using defined names (06:42) 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 This video answers these common search terms: Alt+F11 insert module append character to new formula cells selection do the math in the text episode 135 Evaluate function ignore certain characters Math symbols in spreadsheet MID function SUBSTITUTE function VBA Macro Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153591/ Today's duel involves evaluating math stored inside a text cell such as "1.35x2MVA+3x1.35MVA". Bill and Mike head off in different directions, with Bill doing a VBA macro to extract the digits from the cell and Mike using an Excel 4 Macro function to EVALUATE. Also - learn why Ctrl+F11 inserts a Macro sheet and how you can use the macro sheet to test your EVALUATE function.
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920