CA Rishabh Pugalia (ExcelNext)
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.
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).
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: http://blog.yodalearning.com/2015/06/19/tricks-of-text-to-columns-in-ms-excel/
CAclubindia
