Excel - Formula to Maybe Add Zero Prefix and Dash Suffix - Excel - Episode 1554
Microsoft Excel Tutorial - Conditionally add a prefix of 0 and a suffix of a dash depending on the data.
Welcome back to another exciting episode of Dueling Excel, where we pit our Excel skills against each other to solve your toughest spreadsheet challenges. I'm Bill Jelen, also known as MrExcel, and I'm joined by my esteemed colleague, Mike Girvin from Excel Is Fun. In this episode, we tackle a question sent in from YouTube about adding zeros and dashes to a series of numbers based on the first digit.
At first, I thought we could solve this with a custom number format, but unfortunately, it didn't work for all the numbers. So, we turned to an IF statement to handle both conditions - if the first digit is a 3, we add a zero at the front and a dash after the 4th digit, and if it's not a 3, we add a zero at the front and a dash after the 3rd digit.
Mike had a similar idea, but he took it a step further by using the TEXT function to apply a custom number format within a formula. He also used the REPT function to vary the number of zeros based on the length of the number. And to insert the dash, he used the REPLACE function, which allows you to specify the starting position and the number of characters to replace.
I was blown away by Mike's use of the REPLACE function and how he counted the character position to insert the dash. It's a great trick to have in your Excel arsenal. So, whether you prefer the IF statement or the TEXT and REPLACE functions, we hope you found this episode helpful. Don't forget to subscribe to our channels for more Excel tips and tricks, and we'll see you next week for another exciting 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-...
Table of Contents:
(00:00) Introduction
(00:10) Formatting with custom number format
(00:30) Using IF statement for different conditions
(01:25) Alternative solution using TEXT function
(02:39) Mike's solution using REPLACE function
(06:38) 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:
Calculate current streak
Checkbox formatting in Excel
Custom number format in Excel
Formula for calculating current streak
Formula for finding the last zero
Habit tracker
Handling a streak with no activity
How to find the last one in a list
Motivation tool in Excel
Using Webdings and Wingdings fonts
XLOOKUP formula
XMATCH formula
Today's Duel comes from a YouTube viewer who - depending on the original configuration of digits - would like to add "0"s and " - "s based on the first digit of the Number that has been recorded. Follow along with Episode #1554 as Mike 'ExcelisFun' Girvin and Bill 'MrExcel' Jelen offer their means to the solution.
Dueling Excel Podcast #106...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] http://www.mrexcel.com/slayingdragons...
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! http://www.mrexcel.com/learn2010/LE20...
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...