Excel - How To Insert An Apostrophe Before Each Number In Excel - Duel 170 - Episode 1950
Microsoft Excel Tutorial: Quickly add an apostrophe before every number in Excel.
Welcome to another exciting episode of Dueling Excel! In this episode, Bill Jelen from MrExcel and Mike Girvin from Excel Is Fun will be tackling a common issue faced by many Excel users - adding an apostrophe before every number. This is our 170th duel and we have a great solution for you.
The question for this episode comes from Michael, who has 20,000 rows of data with an Asset ID in column A. He needs to import this data into another software, but the importer requires an apostrophe before the Asset ID. Bill will be showing us how to solve this problem, but before that, let's talk about the purpose of an apostrophe in Excel.
As Bill demonstrates, an apostrophe before a number tells Excel to treat it as text instead of a number. This was a feature inherited from Lotus 123, where the ^ symbol was used to center a number and the " symbol was used to right-justify a number. Interestingly, if you turn on the Transition Navigation Keys in Excel, these symbols will also work. This leads us to wonder if the import software in question was written in the days of Lotus 123, where the apostrophe was used to denote a text cell.
Now, let's get to the solution. Bill will be using VBA to add an apostrophe before every number. He will insert a module and write three lines of code to achieve this. But Mike has a different approach using Excel 2013's Flash Fill feature. He will insert a column, copy and paste the numbers, and then use Flash Fill to automatically add the apostrophe. This is a quick and easy solution, but it only works if you have a heading and a consistent pattern in your data.
Thank you for tuning in to this episode of Dueling Excel. We hope you found these solutions helpful and we'll see you next time for more exciting duels from MrExcel and Excel Is Fun! Don't forget to like, comment, and subscribe for more Excel tips and tricks.
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) Theme Music
(00:30) Put an Apostrophe Before Every Number
(00:44) Explanation of the problem and the use of apostrophes in Excel
(01:03) Discussion on Lotus 123 and its compatibility with Excel
(02:32) VBA solution to add apostrophes before numbers
(03:24) Alternative solution using Flash Fill in Excel 2013
(04:30) 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 #excelflashfill #camelcase
This video answers these common search terms:
how to add apostrophe to numbers in excel
how to add apostrophe before a number in excel
how to apostrophe to in excel column
how to add apostrophe in excel before number
how to insert an apostrophe before a number in excel
how to place apostrophe on text in excel
why to add leading apostrophe in excel
how to add an apostrophe in front of a number in excel
how to add apostrophe in excel
how to add apostrophe to numbers in excel
using an apostrophe in excel
A question from Michael: he has 20000 cells with numbers and needs to add an apostrophe before each number. F2 - Home - ' - Enter would be way too tedious. Bill and Mike compare two of the many methods to solve this. Add a comment with how you would quickly solve this problem!
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
Watch video Excel - How To Insert An Apostrophe Before Each Number In Excel - Duel 170 - Episode 1950 online without registration, duration hours minute second in high quality. This video was added by user MrExcel.com 13 February 2015, don't forget to share it with your friends and acquaintances, it has been viewed on our site 82,902 once and liked it 318 people.