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!
Transcript of the video:
Bill: Hey welcome back, it's time for another Dueling Excel podcast. I'm Bill Jelen from MrExcel, I'll be joined by Mike Girvin from Exel Is Fun. This is our episode 170 - Put an Apostrophe Before Every Number!
Alright, today's question is sent in by Michael, Michael has 20,000 rows of data, over in column A is an Asset ID. He needs to send this data from Excel into some other software, and the importer for that software is expecting an apostrophe before the Asset ID. And before I jump into how to solve this, let's talk about that apostrophe for a second, I'm just going to go to a new workbook here. Of course we type in 123, it's a number, but if we do '123, that tells Excel "Don't treat this as a number, treat this as text!" I'm going to do two other ones, this goes back to the days of Lotus 123, the ^ used to center the number, and the " used to right-justify the number.
Now, it appears here that Excel has only picked up the apostrophe, but really, really obscure, if we go to File, and then Options, and then Advanced, and then in Advanced I'm going to go all the way down to the very bottom, third from the bottom, Transition Navigation keys, turn that back on for Lotus compatibility, then the 123 of course works, '123 still works, ^123 also starts to work, and "123 will work as well. So that's interesting, well, it's interesting in kind of the Cliff Clavin(?) kind of way, I don't think anyone would ever turn on the Formula Valuation keys just to be able to have a quick way to left or center or right justify. But it makes me wonder, when I see Michael's question, if this goes back to the days if this import software was written back in the days of Lotus123, when that apostrophe was used to denote a text cell. I'm guessing, Michael didn't say that he has to export to a CSV, and that ' must get exported out there. So, how am I going to solve this?
I'm going to go to VBA, Alt+F11, I'll insert a module, and these three tiny lines of code here. For Each cell In Selection, the cell.Formula = "'" & cell.Value , and then Next. Right, Alt+F11 to go back to Excel. I'm going to assign it to a shortcut key, so Alt+F8 will get me a list of all my macros, there's my macro called AddIt, Options. I'm just going to do CTRL+T for text, even though that already makes things into a table. This is, you know, kind of a one-time-use thing, and then it'll go away. Alright, so all I have to do then is select my range of cells, CTRL+Shift+Down arrow, and press CTRL+T. And just like that, we've added the apostrophe! Alright Mike, let's see what you have!
Mike: Thanks MrExcel! VBA, you know, before Excel 2013 I don't even know how I would have done this without VBA, but watch this, Excel 2013 rocks! I'm going to Alt I C, to insert a column, Right arrow CTRL C Left arrow CTRL V Down arrow, and watch this apostrophe, and I'm looking at the number 90513, Enter ' ! And you are not going to believe this, Flash Fill picks it up! If I hit Enter, I will accept this ghost list, Enter! Now Flash Fill doesn't work for everything but, CTRL+Down, if you look at every single one, every single one is exactly perfect. CTRL+Home, because if you're doing something simple like inserting a character with these five numbers to the right, it works perfectly! Now, right arrow and Alt E D for delete, C for column, and Enter, there you go. Throw it back to MrExcel!
Bill: Hey, that's brilliant. Like, Flash Fill!
Of course, you have to have the heading up there, adding be anything you want, and then "Any text and", that number, 90513. But I was surprised that it worked for you because Chad at Microsoft who worked on Flash Fill, says that he doesn't like to automatically do Flash Fill for numbers, because it's too hard for Excel to get confused, and sure enough it's not kicking in here. But when it doesn't kick in, and you know that it should be able to kick in, I'm just going to do CTRL+E, and they will fill down. There you go, a couple of cool, different ways to solve this problem.
Well thank you everyone for stopping by, we'll see you next time for another Dueling Excel podcast from MrExcel and excelisfun!
Alright, today's question is sent in by Michael, Michael has 20,000 rows of data, over in column A is an Asset ID. He needs to send this data from Excel into some other software, and the importer for that software is expecting an apostrophe before the Asset ID. And before I jump into how to solve this, let's talk about that apostrophe for a second, I'm just going to go to a new workbook here. Of course we type in 123, it's a number, but if we do '123, that tells Excel "Don't treat this as a number, treat this as text!" I'm going to do two other ones, this goes back to the days of Lotus 123, the ^ used to center the number, and the " used to right-justify the number.
Now, it appears here that Excel has only picked up the apostrophe, but really, really obscure, if we go to File, and then Options, and then Advanced, and then in Advanced I'm going to go all the way down to the very bottom, third from the bottom, Transition Navigation keys, turn that back on for Lotus compatibility, then the 123 of course works, '123 still works, ^123 also starts to work, and "123 will work as well. So that's interesting, well, it's interesting in kind of the Cliff Clavin(?) kind of way, I don't think anyone would ever turn on the Formula Valuation keys just to be able to have a quick way to left or center or right justify. But it makes me wonder, when I see Michael's question, if this goes back to the days if this import software was written back in the days of Lotus123, when that apostrophe was used to denote a text cell. I'm guessing, Michael didn't say that he has to export to a CSV, and that ' must get exported out there. So, how am I going to solve this?
I'm going to go to VBA, Alt+F11, I'll insert a module, and these three tiny lines of code here. For Each cell In Selection, the cell.Formula = "'" & cell.Value , and then Next. Right, Alt+F11 to go back to Excel. I'm going to assign it to a shortcut key, so Alt+F8 will get me a list of all my macros, there's my macro called AddIt, Options. I'm just going to do CTRL+T for text, even though that already makes things into a table. This is, you know, kind of a one-time-use thing, and then it'll go away. Alright, so all I have to do then is select my range of cells, CTRL+Shift+Down arrow, and press CTRL+T. And just like that, we've added the apostrophe! Alright Mike, let's see what you have!
Mike: Thanks MrExcel! VBA, you know, before Excel 2013 I don't even know how I would have done this without VBA, but watch this, Excel 2013 rocks! I'm going to Alt I C, to insert a column, Right arrow CTRL C Left arrow CTRL V Down arrow, and watch this apostrophe, and I'm looking at the number 90513, Enter ' ! And you are not going to believe this, Flash Fill picks it up! If I hit Enter, I will accept this ghost list, Enter! Now Flash Fill doesn't work for everything but, CTRL+Down, if you look at every single one, every single one is exactly perfect. CTRL+Home, because if you're doing something simple like inserting a character with these five numbers to the right, it works perfectly! Now, right arrow and Alt E D for delete, C for column, and Enter, there you go. Throw it back to MrExcel!
Bill: Hey, that's brilliant. Like, Flash Fill!
Of course, you have to have the heading up there, adding be anything you want, and then "Any text and", that number, 90513. But I was surprised that it worked for you because Chad at Microsoft who worked on Flash Fill, says that he doesn't like to automatically do Flash Fill for numbers, because it's too hard for Excel to get confused, and sure enough it's not kicking in here. But when it doesn't kick in, and you know that it should be able to kick in, I'm just going to do CTRL+E, and they will fill down. There you go, a couple of cool, different ways to solve this problem.
Well thank you everyone for stopping by, we'll see you next time for another Dueling Excel podcast from MrExcel and excelisfun!
