Replacing Numbers with Letters

[Darth_Elicious]

Hello all,



I am trying to make a system to label photos I take in the field for my job. The numbers follow the format "[Name]_[Letter] Facing [Direction]", or something like "XX12345_a Facing North".

I already asked about getting the numbers to line up correctly, so I can get the names to follow a _1, _2, _3... format, which works great (https://www.accessforums.net/showthr...027#post354027)

The problem is sometimes clients want it labeled with letters instead of numbers. I have found a way to use REPLACE to get the result I want, like this:

Photo	Resource	Number	Letter
1233	XX12234	1	a
1234	XX12234	2	b
1235	XX12234	3	c
1236	XX12234	4	d
1237	XX12356	1	a
1238	XX12356	2	b
1239	XX12677	1	a

Here is the formula I have:

(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(R EPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REP LACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLA CE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (REPLACE([N],"10","j"),11","k"),"12","l"),"13","m"),"14","n"), "15","o"),"16","p"),"17","q"),"18","r"),"19","s"), "20","t"),"21","u"),"22","v"),"23","w"),"24","x"), "25","y"),"26","z"),"1","a"),"2","b"),"3","c"),"4" ,"d"),"5","e"),"6","f"),"7","g"),"8","h"),"9","i") )

That's 26 total REPLACES for the alphabet . The problem is it slows access WAAAAAY down when it loads. Is there a less demanding way to accomplish this?

Thanks!

[andy49]

Why not create a table with 1-26 and a-z

Then link that table to your current one joining in the 3rd column


Sent from my iPhone using Tapatalk

[pbaldy]

Or

Chr(96 + [N])

[Darth_Elicious]

Wow, that was easy. THANKS!!

[pbaldy]

Whoever's solution you used was happy to help.

[andy49]

Ditto!! [emoji6]


Sent from my iPhone using Tapatalk

[Darth_Elicious]

I used yours Pbaldy, but Andy's was a good way too. Thanks again! If it's not too much trouble, out of curiosity, can you explain to me what the Chr(96 + [N]) command is actually doing? That is a very simple line for such a seemingly complex function.

Thank you!

[pbaldy]

Andy recommended a table, which normally would be a good solution, but in this case there already is one (in a manner of speaking):

https://www.techonthenet.com/ascii/chart.php

97 is the decimal value for "a", so my solution simply adds your number to 96 and converts the result to the ascii code.