SOLVED

Change numbers in column to text

%3CLINGO-SUB%20id%3D%22lingo-sub-1443119%22%20slang%3D%22en-US%22%3EChange%20numbers%20in%20column%20to%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443119%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20some%20of%20you%20a%20simple%20issue%2C%20but%20for%20i%20am%20struggling%20for%20long%20to%20do%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20two%20columns%20which%20i%20want%20to%20use%3A%20the%20one%20with%20numbers%20of%20our%20shops%20en%20one%20with%20hours%20people%20worked%20an%20secific%20month.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20for%20example%3A%3C%2FP%3E%3CP%3EShop%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Hours%3C%2FP%3E%3CP%3E01%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20226%3C%2FP%3E%3CP%3E01%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20224%3C%2FP%3E%3CP%3E02%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20254%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20i%20want%20to%20change%20the%20number%20to%20the%20shops%20into%20their%20names.%20So%20that%2001%20will%20become%20%22Amersfoort%22.%3C%2FP%3E%3CP%3EBecause%20of%20the%20very%20large%20amount%20of%20data%20i%20want%20to%20automatic%20this.%20Anyone%20an%20idea%20how%20to%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreetings%2C%3C%2FP%3E%3CP%3EPaul%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1443119%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1443619%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20numbers%20in%20column%20to%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443619%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F486386%22%20target%3D%22_blank%22%3E%40PaulD98L%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPaul%2C%20if%20you%20have%20separate%20table%20which%20maps%20shop%20numbers%20and%20names%2C%20you%20may%20VLOOKUP%20or%20INDEX%2FMATCH%20it%20to%20return%20names%20into%20the%20main%20table.%3C%2FP%3E%0A%3CP%3EIf%20Excel%20has%20no%20idea%20about%20such%20mapping%2C%20when%20manually%20-%20Ctrl%2BH%20and%20change%20one%20number%20by%20another.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1444103%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20numbers%20in%20column%20to%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1444103%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20answer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20created%20a%20table%20with%20our%20shops%20and%20try%20to%20use%20the%20formula.%3C%2FP%3E%3CP%3EIt%20fails%2C%20how%20should%20i%20use%20this%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3Dindex(matrix%20with%20shops%3Bmatch(number%3Bnumbers%20of%20shops%3B0))%3C%2FP%3E%3CP%3EI%20get%20the%20result%20of%20the%20shops%20in%20a%20row%2C%20but%20it%20doesn't%20convert%20the%20numbers%20into%20the%20name%20of%20the%20shops.%20What%20am%20i%20missing%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreetings%2C%3C%2FP%3E%3CP%3EPaul%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1444919%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20numbers%20in%20column%20to%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1444919%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F486386%22%20target%3D%22_blank%22%3E%40PaulD98L%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPaul%2C%20let%20me%20illustrate%20on%20this%20simple%20model%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20595px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F196889i9446B4C5B9E725F9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EEven%20better%20to%20use%20Table%20for%20the%20mappings%2C%20it's%20easier%20to%20support%20and%20not%20necessary%20to%20adjust%20formulas%20for%20expanded%20ranges%20or%20use%20dynamic%20ranges%20formulas.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20699px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F196917iEB31D9DDFEBB097C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eif%20we%20name%20the%20table%20as%20%3CSTRONG%3EshopNames%3C%2FSTRONG%3E.%3C%2FP%3E%0A%3CP%3EPlease%20check%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1520644%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20numbers%20in%20column%20to%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1520644%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%20this%20helped%20a%20lot.%20I%20am%20using%20this%20a%20lot%20now.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreetings%2C%3C%2FP%3E%3CP%3EPaul%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1521645%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20numbers%20in%20column%20to%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1521645%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F486386%22%20target%3D%22_blank%22%3E%40PaulD98L%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPaul%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello,

 

For some of you a simple issue, but for i am struggling for long to do this.

 

I have two columns which i want to use: the one with numbers of our shops en one with hours people worked an secific month.

 

So for example:

Shop                    Hours

01                        226

01                        224

02                        254

 

Now i want to change the number to the shops into their names. So that 01 will become "Amersfoort".

Because of the very large amount of data i want to automatic this. Anyone an idea how to do this?

 

Thanks in advance!

 

Greetings,

Paul

 

 

5 Replies
Highlighted

@PaulD98L 

Paul, if you have separate table which maps shop numbers and names, you may VLOOKUP or INDEX/MATCH it to return names into the main table.

If Excel has no idea about such mapping, when manually - Ctrl+H and change one number by another.

Highlighted

@Sergei Baklan 

 

Thanks for your answer.

 

I created a table with our shops and try to use the formula.

It fails, how should i use this formula?

 

=index(matrix with shops;match(number;numbers of shops;0))

I get the result of the shops in a row, but it doesn't convert the numbers into the name of the shops. What am i missing?

 

 

Greetings,

Paul

Highlighted
Best Response confirmed by PaulD98L (New Contributor)
Solution

@PaulD98L 

Paul, let me illustrate on this simple model

image.png

Even better to use Table for the mappings, it's easier to support and not necessary to adjust formulas for expanded ranges or use dynamic ranges formulas. 

That could be like

image.png

if we name the table as shopNames.

Please check in attached file.

Highlighted

@Sergei Baklan 

Thanks! this helped a lot. I am using this a lot now.

 

Greetings,

Paul

Highlighted

@PaulD98L 

Paul, glad to help