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