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.
Convert multiple columns of numbers into text at once
{ 5 Comments }