Converting numbers with trailing minus sign into negative numbers.
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.
Now Splitting name into two columns as First Name and Last Name is easy. Read the Case study and examples based on it. Click now:
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 in Column 2 as given below.
Also, now you can, ‘Transfer Single Columnar Data into Multiple Columns. To know more click here
Hidden trick of Text to Column in Excel
Step 1: Choose the data set (Column 1).
Step 2: Go to Data tab
Step 3: Click on Text to Columns.
Step 4: Choose Delimited and press next.
Step 5: Uncheck all the boxes in Text to Columns wizard and then press next.
Step 6. Click Finish in Text to Columns wizard.
Step 7: You will see the final output with minus sign in proper place (leading or prefix).
Conclusion: Now your result for both the columns is same.
We have solved another interesting query of quiet a few people, in case if you wish to convert an invalid date format into a valid date format. Click here: