Excel Formula to Replace Numbers List with Default Text

Question

Using Office 365 Excel

On a regular basis I download a csv file from a remote application, the file contains a column of unique numbers. There could be 50 rows in this column.

I would like to substitute these rows of numbers with text. Each number corresponds to a value e.g.

0 = car
421 = bus
12 = boat

I have a predefined text list of each number and it's corresponding value. Currently I'm doing a find and replace on each number to replace it with it's corresponding value' but it's very inefficient.

Is there a more efficient way I can achieve this, with some sort of saved formula that I can run each time I download the csv? When I download the default csv ideally i'd like to run some formula that I have saved, rather than copy / paste a lot of things between sheets etc.

An example below of the default csv (showing only three rows for brevity);

+-----+----------------+
| id  | another column |
+-----+----------------+
| 0   | text           |
+-----+----------------+
| 421 | text           |
+-----+----------------+
| 12  | text           |
+-----+----------------+
| etc | etc            |
+-----+----------------+

I would like to run a formula that will replace the numbers with text like below.

+------+----------------+
| id   | another column |
+------+----------------+
| car  | text           |
+------+----------------+
| bus  | text           |
+------+----------------+
| boat | text           |
+------+----------------+
| etc  | etc            |
+------+----------------+

Any other suggestions are also welcome, however preferred method is via Excel.

Answer 1

You can do it only with Excel: create a table into a new sheet/area where you can match ids and texts that you have to replace, i.e.: ( into a new sheet named 'MyTalbe') A |B 0 | car 421 | bus 12 | boat ...etc etc

into your db create a new colum with this formula: index( 'MyTable'!B:B; match(A1; 'MyTable'!A:A; 0))