Some Trailing Minus Numbers

Learn Excel from MrExcel Podcast, Episode 2201: Some Trailing Minus Numbers.

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Another question from my Nashville seminar for the Lincoln Trail Council, the IMA. Someone's downloading data from Oracle, and Oracle is making the negative numbers have a trailing minus number-- so the minus is appearing after the number. Oh, geez.

And of course, you could go in and edit each one of these one at a time-- that would be really, really tedious-- or you could turn to a formula like this, where we look to see if the rightmost character of Column B is a minus sign. If it is, then we take -1 times the left of B2, all of the characters except for one, otherwise, give us B2; =IF(RIGHT(B2, 1)="-", -1*LEFT(B2,LEN(B2)-1), B2). And while that works, that's not a fun way to go.

Alright. And so, instead-- undo, undo-- we're going to go with something built-in. So, I start here in B2, Ctrl+Shift+Down Arrow, Ctrl+ Backspace, and go to Data, Text to Columns, and we're going to choose either Delimiter or Fixed width-- it does not matter, I want this to stay a single column, I don't want it to break into columns at all. So step 2, make sure that we only have one column; step 3-- now this is cool over here-- under Advanced, there are three settings, specifies the Decimal and Thousand separator, and right here this is the one we want "Trailing minus for negative numbers". Click OK, and then Finish.

But I want you to notice that, “Trailing minus for negative numbers”, is already turned on by default-- It is the default, alright? So we don't even have to come in here and go through each of the steps. Instead, we can simply run, Data, Text to Columns, and click Finish.

Alright, now, there are a couple of different ways to do this: The way that, I guess, you would do it if you were new to Excel, is the press and release Alt, and you see up here that Data is the A-- so, Alt+A and then E, and then simply click Finish. So Alt+A, E, F-- but that's hard to remember, so I still use the Excel 2003 dialog where Text to Column was on the Data tab. So it was Alt+D. So, in the first case, it's press and release Alt, and then A, E, F; mine, hold down Alt, and press D to open Data. On the Excel 2003 Data menu, T was already used for Table-- data table-- so Text to Columns was E, a second letter-- so Alt+D, E, F. I like that, three letters in sequence, easy to remember. Just choose the whole column like this, Alt+D, E, F, BAM! All of those negative numbers have been fixed.

That's a trick-- the Alt+D, E, F, trick is a trick that I learned here on the YouTube channel almost 1500 videos ago, when I was trying to convert a column of text numbers to real numbers. Someone pointed out Alt+D, E, F, would do that. Now that trick-- the Alt+D, E, F, trick-- is definitely in this book, MrExcel LIVe, The 54 Greatest Tips of All Time. Click that "I" in the top right-hand corner for more information about that.

Alright, wrap-up from this Episode: A question from my Nashville Power Excel seminar-- how to deal with downloaded data where some cells contain trailing minus numbers. You could try and write a crazy formula, but we're not going to do that-- Text to Columns is a fast way to deal with this. Choose the whole column; Data; Text to Columns; Delimiters; Next; make sure there are no lines; Next; and then open Advanced and choose "Trailing minus numbers". But, hey, Trailing minus is already the default, so there's a faster way: You select the whole column and do Alt+D, followed by E, F, or press and release Alt, A, followed by E, followed by F-- Alt D, E, F, is definitely easier to remember because it's three letters in sequence.

To download the workbook from today's video, visit that URL down in the YouTube description. Also in the YouTube description, the link to upcoming Power Excel seminars. I would love to see you in one of my upcoming seminars.

Hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.