Data clean-up techniques in Excel: Fixing trailing minus signs

Sometimes it may happen that your imported data will show as the following screenshot:

Data clean-up techniques in Excel: Fixing trailing minus signs

Some data have minus signs at the end, trailing minus signs.

Excel does not convert these values automatically. Excel considers them to be non-numeric texts.

The solution is really simple. Here it is:

  1. Select the cells that have the trailing minus signs. Your selection may also have positive and negative values. -3256 is a negative value, but Excel considers 3256- as a text value.
  2. Choose Data ➪ Data Tools ➪ Text to Columns. The Text to Columns dialog box will appear.
  3. Click Finish.
Data clean-up techniques in Excel: Fixing trailing minus signs

Step 1: Select the cell range Step 2: Choose Data ➪ Data Tools ➪ Text to Columns and Step 3: Click Finish

This procedure works because there is a default setting in the Advanced Text Import Settings dialog box. To display this dialog box, go to Step 3 in the Convert Text to Columns Wizard dialog box and click Advanced.

Data clean-up techniques in Excel: Fixing trailing minus signs

Advanced Text Import Settings. dialog box. The Trailing Minus for Negative Numbers option makes it very easy to fix trailing minus signs in a range of data.

Happy Excelling 🙂

Read More…

Data clean-up techniques in Excel: Adding text to cells

Data clean-up techniques in Excel: Filling blank cells

Data clean-up techniques in Excel: Changing vertical data to horizontal data

Data clean-up techniques in Excel: Classifying values

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo