Excel Sum Challenge: Sum Some Trailing Minus Numbers - Episode 2201
1,721 views · Published 10 May 2018 · 4:24 · Indexed 28 October 2026
Channel: MrExcel.com · 2018 · Education
Microsoft Excel Tutorial: Fix Numbers with Trailing Minus Sign Welcome to another episode of the MrExcel netcast, where we bring you the best tips and tricks for mastering Excel. In this episode, we will be discussing how to deal with trailing minus numbers in Excel. This is a common issue that many users face when downloading data from external sources, such as Oracle. Have you ever encountered a situation where the negative numbers in your Excel sheet have a trailing minus sign, appearing after the number? This can be a frustrating and time-consuming problem to fix, especially if you have a large dataset. But fear not, because in this video, I will show you a simple and efficient solution to this issue. Instead of manually editing each cell, which can be tedious and prone to errors, we will use a built-in feature in Excel called Text to Columns. This feature allows us to quickly and easily convert the trailing minus numbers into proper negative numbers. All you have to do is select the column with the trailing minus numbers, go to Data, and then click on Text to Columns. From there, choose either Delimiter or Fixed width, and in the Advanced settings, make sure to select "Trailing minus for negative numbers". Click Finish, and voila! Your numbers will now be properly formatted. But wait, there's an even faster way to do this. Instead of going through the steps in the Text to Columns dialog box, you can use a shortcut by pressing Alt+D, E, F. This will instantly convert all the trailing minus numbers in your selected column. This trick was actually shared by one of our viewers in the comments section of a previous video, and it has been a game-changer for many Excel users. If you want to learn more tips and tricks like this, be sure to check out my book, MrExcel LIVe, The 54 Greatest Tips of All Time. You can find the link in the top right-hand corner of this video. And don't forget to download the workbook from today's video, which is also linked in the description below. Thank you for tuning in to this episode of the MrExcel netcast. I hope this tip has been helpful for you. Don't forget to subscribe to our channel and hit the notification bell so you don't miss out on any future episodes. And if you want to take your Excel skills to the next level, be sure to check out our upcoming Power Excel seminars. I would love to see you there. Until next time, this is Bill Jelen, signing off. 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-on-youtube/ #excel #microsoft #microsoftexcel #exceltips #exceltricks #excelhacks #evergreen #excelformatting #numberformatting This video answers these common search terms: excel move trailing minus sign negative numbers with trailing hyphen in excel fix excel cell formatting for minus sign remove dash from the end of numbers in excel correct negative number display in excel excel trailing minus symbol problem hyphen at the end of numbers in excel Table of Contents: (00:00) Fix Trailing Minus Numbers After the Number (00:38) Solving with a formula (01:00) Using Text to Columns is Easier (02:00) Shortcut using Alt+D, E, F or Alt+A, E, F (03:00) Wrap-up and summary of solution (04:00) Clicking Like really helps the algorithm A question from my Nashville Power Excel seminar: How to deal with downloaded data where some cells contain trailing minus numbers? It is tempting to fix them manually or to use =IF(RIGHT(B2,1)="-",-1*LEFT(B2,LEN(B2)-1),B2) Text to Columns is a fast way to deal with this Choose the whole column Data, Text to Columns, Delimited, Next, Make sure there are no lines, Next, Options: Trailing Minus In fact, since Trailing Minus is the default, there is a faster way: Select whole column and Alt+D E F To download this workbook: https://www.mrexcel.com/download-center/2018/05/some-trailing-minus-numbers.xlsx Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1154030/
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920