Some Trailing Minus Numbers
May 10, 2018 - by Bill Jelen
A question from my Nashville Power Excel seminar: I download data from the system and some of my numbers have a trailing minus sign. How do I convert only the ones with a trailing minus sign?
See the figure below. I used to run in to trailing minus numbers in 1987 in COBOL. It is hard to believe they are still plaguing us to this day.
It is tempting to fix them manually. It is tempting to turn to a lengthy formula such as
The fast solution is buried in Text to Columns. Select the data in column B. Select Data, Text to Columns. Click Next to move to Step 2. In Step 2, verify that you are *not* splitting the data into more than one column.
Click Next. In Step 3 of the Wizard, click Advanced. There is a setting to handle Trailing Minus Numbers.
But here is the awesome thing: Trailing Minus is selected by default. That means you do not have to go through all of the Text to Columns steps. You can simply open Text to Columns and click Finish. You can do this with three keystrokes: Alt + DEF. It is easy to remember, since it is three letters in sequence. Press Alt + D. At this point, you can let go of Alt and press E then press F. The Alt + D is the Excel 2003 Data menu. The E is Text to Columns. F is Finish.
I learned the Alt + DEF trick about 1500 videos ago on my YouTube channel. I posted a video about converting text numbers to numbers and someone gave me Alt + DEF in the YouTube Comments.
The result: All of the trailing minus sign numbers change from text to negative 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.
Download Excel File
To download the excel file: some-trailing-minus-numbers.xlsx
There are always extra tips that show up in the YouTube video, so watch it if you have time. For example, using Ctrl + Backspace to bring the active cell back in to view is in today's video.
Excel Thought Of the Day
I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:
"Spreadsheets grow like bushes, they need regular trimming."
Title Photo: Guillaume Jaillet on Unsplash