Convert numbers to sentences

[christianlie]

Hello. I have cells with numbers representing a sentence. Every cell with the similar number are to be converted to the same sentence. Usually, this would be an easy "find and replace" job, but there are about 3000 different numbers / sentences. This would be fine, if it was a one time job, but this convertion has to be carried out several times in the future as more punching is finished. Is there a way to program Excel to automatically convert certain numbers to speceific sentences right away or in one automated process after punching has been made? Thanks.

[zbor]

How is your workbook organised.

If like this;

Please Login or Register  to view this content.

You can use VLOOKUP function:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])


Making [range_lookup] FALSE your table doesn't need to be sorted and you can match only identical numbers (wich can't be more than once)

[Palmetto]

I have cells with numbers representing a sentence. Every cell with the similar number are to be converted to the same sentence.

This statement seems to imply that you may have different sentences associated with a single number.

Example:
1 The quick brown fox
1 It was a dark and stormy night

If the above assumption is true, which is the correct sentence to associate with the number?

I would start first by using Advanced Filter to extract a list of all the unique values into another list in order find and correct these errors. Once you have the list cleaned up create a master look up table and use the VLOOKUP function suggested by zbor.

[christianlie]

Hello. I have as follows:

A
124038
124039
129027
124038
129028

I want thos to be:
A
The fast fox
The fast pig
The slow whale
The fast fox
The slow mouse

All numbers are 6 digits, they are all repeated several times in the CSV. sheet. There are about 3000 unique six digit combos, whic are to be replaced with about 3000 unique sentences. Over the next year, this process are to be repeated many times, so it would be nice not having to do the replacing job every time, but to let Excel know that number 124038 is supposed to be "The fast fox" every time without me converting it manually.

[davesexcel]

Vlookup would be a good idea like the zbor had suggested.

All sorts of threads on Vlookup in this forum, just search Vlookup.

[zbor]

According to your example you can separate into two tables, to get better overview.

124038
124039
129027
124038
129028

If I'm getting it right, 124 stands for The fast and 129 stands for The slow.
Then you can look other part of the word 038 for fox. Etc

Maybe best way would be to upload example (avoid sensitive data)

[christianlie]

Actually, the numbers can not be decyphered into anything. Every unique 6 digit number, has to be replaced by one sentence which will vary for every 6 digit number. This process is to be made several times over a long time, which is why I would like to have this process more or less automated after I have done it once. I asume I have to do it once to program it somehow. What is Vlookup? Thanks.

[zbor]

I know that numbers are uniqe. I just assumed, over above example, that they can be gruped somehow.

123 456
123 654
456 123

are all uniqe, but first two are in same sub-group of 123 xxx

But if they can't it doesnt matter.

VLOOKUP is a excel function.

I write you sintax above and for more informations see somewhere on the web.
For example youtube:

http://www.youtube.com/watch?v=GZX2iXfqALs