Flip minus sign from right to left in a multi column range

{ 1 Comments }

In range B3:B7, assume the following values: 12,34-,23,6-,8.  As can be observed, the minus sign is appearing after the number thereby treating 34- and 6- as text values.  The objective is to place the minus sign before the number so that all values in range B3:B7 can be added.  Since data is in one column only, one can simply select B3:B7 and go to Data > Text to columns > Finish.  This process will cause 34- and 6- to become -34 and -6.

The question that arises now is what can be done if values are in range C3:G15.  Assume values in C3:G15 are:

1. In cells C3, C6 and C10, values are 12, 23 and -256
2. In cell D14, value is 23-
3. In cells E6 and E11, values are 45- and 45-
4. In cell F4, value is Text1
5. In cell G15, value is 47
6. Other cells of C3:G15 are blank

If one selects C3:G15 and goes to Data > Text to columns, a message pops up saying “Microsoft Excel can only convert one column at a time……”.  The alternative is to run Data > Text to columns five times – one for each column.  Given a large number of columns, this could be a time consuming activity.

Herein is the process to flip the minus sign from right to left for multiple columns without using Data > Text to columns or VBA (macros):

1. In cell H1, type 1
2. In cell I1, enter =$H$1
3. Copy cell I1 and select range C3:G15
4. Right click, select Paste Special > Multiply.  This step will convert all cells in the range to formulas except values in cells D14, E6, E11 and F4.  All blank cells will now carry 0 values
5. In cell K1, type -1 and copy it
6. Select range C3:G15 and press Ctrl+G > Special > Constants.  This step will select cells D14, E6, E11 and F4
7. Press Ctrl+H
8. In the Find what box, type – and leave the Replace With box blank.  Click on Replace All
9. Right click, select Paste Special > Multiply.  This step will convert 23-, 45- and 45- to -23, -45 and -45 respectively
10. To remove the 0 values now, press Ctrl+H.  In the Find what box, enter =0*($H$1) and leave the Replace With box blank.  Click on Replace All.
11. You may now delete cells I1 and K1

Leave a Comment Cancel reply

Your email address will not be published.

*