GST Plus - Get Daily updates,support, whatsapp Group & reply to GST Notices etc.!! Call : 011-411-70713 !!


Share on Facebook

Share on Twitter

Share on LinkedIn

Share on Email

Share More

There are series of numbers, wherein some of the numbers are ending with minus sign (-). Now, if you want to apply sum, min, max or average functions, it will not work correctly as Excel cannot read 3789- as a negative.

Although, it is rare to see this kind of data set in today’s world, if you are working with old ERP systems or legacy MIS reports, then you will see such cases. Our objective is to convert the data of Column 1 into something that is shown under Column 2 as given below.

Converting numbers with minus sign into negative numbers in Excel

Let’s see how to achieve this using a hidden trick of Text to Column.

Step 1: Choose the data set (Column 1). Go to Data tab and then Click on Text to Columns. Then choose Delimited and press next. Uncheck all the boxes in Step 2 of 3 of Text to Columns wizard and then press next. Click Finish under Step 3 of 3 of Text to Columns wizard. You will see the final output with minus sign in proper place (leading or prefix).

Excel Converting numbers

Now your result for both the columns is same.

Recommended course to learn MS Office tools like Advanced Excel, Charts, PowerPoint Ninja, VBA Macros and Word for Finance Professionals.
Click here: /coaching/584-ms-office-bundle.asp

Canonical link:

"Loved reading this piece by CA Rishabh Pugalia (ExcelNext)?
Join CAclubindia's network for Daily Articles, News Updates, Forum Threads, Judgments, Courses for CA/CS/CMA, Professional Courses and MUCH MORE!"

Tags :

Category Info Technology, Other Articles by - CA Rishabh Pugalia (ExcelNext)