Category: DATA CONVERSION

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

{0 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

Convert multiple columns of numbers into text at once

{4 Comments}

Assume numbers in range C10:C13 as 12,23,34,45.  Assume numbers in range E10:E13 as 56,67,78,89.  For uploading these two columns (along with other columns as well) into an ERP, C10:C13 and E10:E13 need to be converted to text values i.e. numbers stored as text values.

Once way to do this is to:

1. Select C10:C13 and to go Data > Text to columns > Delimited > Next
2. Ensure that all delimiters are unchecked > Next
3. Under column data format, select Text > Finish
4. Repeat steps 1-3 for range E10:E13 as well

As can be observed from the steps outlined above, this would be a time-consuming process because Text to column cannot be run on multiple columns as once.

In order to convert numbers to "numbers stored as text" at once without using VBA or spare columns, try the following:

1. In cell C18, type 1
2. In cell D18, enter =$C$18
3. Copy cell D18
4. Select range C10:C13 and E10:E13 (by holding down the Ctrl key)
5. Right click and select Paste Special > Divide > OK
6. Select range C10:C13 and E10:E13 (by holding down the Ctrl key) and press Ctrl+H
7. Find for = and replace with ' (single quote).  Press Replace All
8. Select range C10:C13 and E10:E13 (by holding down the Ctrl key) and press Ctrl+H
9. Find for /($C$18) and replace with blank.  Press Replace All

The values in range C10:C13 and E10:E13 will be numbers stored as text values.