Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Someone put a bunch of mathematical calculations in column A as text and we want to evaluate those formulas. Mike and Bill provide dueling ways to solve this problem in Episode 1149.
posted by Bill at 9:00 AM
Roger from Columbus Indiana passes along a cool trick for formatting all of the text in a chart at once. Episode 1131 shows you how.
Labels: charts, Formatting, Text
posted by Bill at 9:00 AM
Brett from Australia asks how to format a date to include the words Opening Balance so that he can do a VLOOKUP into a report. The format is easy, the VLOOKUP is tougher. Episode 1115 shows you how.
Labels: DATE, dates, Formatting, Text, VLOOKUP
posted by Bill at 9:00 AM
It is Memorial Day in the U.S. Since most people are out at a picnic instead of at work today, I tackle an incredibly obscure problem. How can we make sure that Hexadecimal numbers have leading zeroes? Episode 766 will take a look at the HEX2DEC, TEXT, and RIGHT functions.
posted by Bill at 9:00 AM
When Romas sent in the question for podcast 751, I couldn't tell from the screenshot if his data actually was date/time or text that looked like a date/time. In Episode 753, I take a look at how to tell if the cell contains a date or something that looks like a date. You will also see how to use the TEXT, LEFT, MID, DATE functions to convert the text date/time to a real date.
posted by Bill at 9:00 AM
Terry called in with a question where she wanted to use a pivot table to show text fields from a database. While this question initially threw me for a loop, we did end up with a workable pivot table that showed off text. Episode 748 shows you how.
Labels: Pivot Table, Text
posted by Bill at 9:00 AM
After importing data, your numbers and dates might be stored as text. While newer versions of Excel allow you to solve the numbers stored as text easily, the exclamation point dropdown never appears for the dates stored as text. Episode 480 takes a look at several methods for solving the problem.
posted by Bill at 9:00 AM
If you try to use the concatenation character to join text with a date, you will not get the results that you expected. Episode 414 shows you how to modify the formula to properly format the date.
Labels: Concatenation, dates, Formatting, Text
posted by Bill at 9:00 AM
Get quick access to all of the text formatting commands by moving your mouse towards the completely invisible mini toolbar! (Who at Microsoft comes up with this stuff?) Episode 413 shows you how to get the shy mini toolbar to appear.
Labels: Formatting, Text, toolbar
posted by Bill at 9:00 AM
How to join text from two columns using the concatenation character. Episode 412 shows you how to do concatenation, plus how to convert upper case to proper case on the fly.
Labels: Concatenation, Text
posted by Bill at 9:00 AM
Rather than static text in the AutoShape, use a formula to add dynamic text to an AutoShape. Episode 370 shows you how to create a shape that shows off the total of all sales made today.
Labels: autoshapes, Dynamic, formula, Text
posted by Bill at 9:00 AM
Create cool headlines by adding text to your autoshape and then formatting it using the drawing toolbar. Episode 369 shows you how.
Labels: autoshapes, Formatting, Text
posted by Bill at 9:00 AM
