[Solved] Convert Number to Letters

[stanmarsh]

hello,

i like to convert numbers to specific letters, these are used as product code, i have seen the number to words http://user.services.openoffice.org/en/ ... =9&t=45719, maybe it can be converted to use the code below,

the code is:

A = 1
B = 2
C = 3
D = 4
E = 5
F = 6
G = 7
H = 8
I = 9
J = 0
K = if the number is equal to the last digit

sample product number 1000 = AJKJ = product code
product number 288933384 = BHKICKCHD = product code (the 333 = CKC not CCC)
product number 3339440-3392 = CKCIDDJ-CKIB = product code (sometimes we use "-")

thanks OOo!

[squenson]

There is a typo:
3339440-3392 = CKCIDKJ-CKIB

Here is a formula, assuming that your product number is in A1:

Code: Select all

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"00","JK"),"11","AK"),"22","BK"),"33","CK"),"44","DK"),"55","EK"),"66","FK"),"77","GK"),"88","HK"),"99","IK"),"0","J"),"1","A"),"2","B"),"3","C"),"4","D"),"5","E"),"6","F"),"7","G"),"8","H"),"9","I")

[Charlie Young]

This is certainly no simpler than Squenson's solution, but I've been playing with it, and I THINK it's working.

We will need a function to concatenate all the elements in an array

Code: Select all

Function Joiner(r) as String

	Dim i as Integer
	Dim j as Integer
	Dim Size(2) as Integer
	Dim s as String
	
	s = ""
	if IsArray(r) then
		Size(1) = UBound(r,1)-LBound(r,1)
		Size(2) = UBound(r,2)-LBound(r,2)
		
		s = ""
		for i = 0 to Size(1)
			for j = 0 to Size(2)
				s = s & r(LBound(r, 1)+i, LBound(r, 2) + j)
			Next j
		Next i
	else
		s = r
	endif
	Joiner = s
end Function

Then an array formula to convert the numerical code in A1 is

Code: Select all

=JOINER(IF(NOT(ISERR(SEARCH("[-\.]";MID(A1;TRANSPOSE(ROW(INDIRECT("A1:"&ADDRESS(LEN(A1);1;4))));1))));MID(A1;TRANSPOSE(ROW(INDIRECT("A1:"&ADDRESS(LEN(A1);1;4))));1);CHOOSE(VALUE(IF(TRANSPOSE(ROW(INDIRECT("A1:"&ADDRESS(LEN(A1);1;4))))>1;IF(MID(A1; :D TRANSPOSE(ROW(INDIRECT("A1:"&ADDRESS(LEN(A1);1;4))));1)=MID(A1;TRANSPOSE(ROW(INDIRECT("A1:"&ADDRESS(LEN(A1);1;4))))-1;1);"10";MID(A1;TRANSPOSE(ROW(INDIRECT("A1:"&ADDRESS(LEN(A1);1;4))));1));MID(A1;TRANSPOSE(ROW(INDIRECT("A1:"&ADDRESS(LEN(A1);1;4))));1)))+1;"J";"A";"B";"C";"D";"E";"F";"G";"H";"I";"K")))

A spreadsheet is attached for your amusement, and simplifications are invited.

Edit: Just noticed I have a problem where the "333" should be "CKC" and not "CKK", stay tuned.

[acknak]

This doesn't help if you have to do the job in a spreadsheet, but somewhat more direct approaches do exist...

• $ perl -pe 'tr/1-90/A-J/; s/(.)\1/$1K/g'
  1000
  AJKJ
  288933384
  BHKICKCHD
  3339440-3392
  CKCIDKJ-CKIB

I'm sure the same tools are available in python, which can be accessed from a Calc formula.

[Charlie Young]

... but somewhat more direct approaches do exist...

I'm sure the same tools are available in python, which can be accessed from a Calc formula.

Yes, and at some point a macro becomes preferable to a formula (though I haven't quite given up yet).

Maybe

Code: Select all

Function lettercodes(r) as String

	Dim i as Integer
	Dim s as String
	Dim m As String
	Dim v As Long
	Dim c As String
	
	s = ""
	for i = 1 to len(r)
		m = mid(r,i,1)
		if InStr("0123456789",m) > 0 then
			v = Val(m) + 1
			c = mid("JABCDEFGHI",v,1)
		else
			c = m
		endif
		if i > 1 then
			m = right(s,1)
			if c <> m then
				s = s & c
			else
				s = s & "K"
			endif
		else
			s = s & c
		endif
	next i
		
	lettercodes = s
end Function

[stanmarsh]

@squenson, Charlie Young and acknak

thanks so much for the help!

i tried the formula of squenson and it was the best solution so far,

@charlie young

thanks for the help with the macro, i like to use the macro but i never had the patience to learn it! i tried the example attachment and i also saw the 333 as CKK not CKC. i use the second script, dont know if i did it right because i just remove the text in joiner and replaced it with the your new code but im getting
BASIC runtime error
Argument in not optional

and the arrow pointed to for i = 0 to len(r)

@acknak
thanks for the help, i dont know how to use it though, can i use that in bash? where can i use that?

[Charlie Young]

@squenson, Charlie Young and acknak

thanks so much for the help!

i tried the formula of squenson and it was the best solution so far,

@charlie young

thanks for the help with the macro, i like to use the macro but i never had the patience to learn it! i tried the example attachment and i also saw the 333 as CKK not CKC. i use the second script, dont know if i did it right because i just remove the text in joiner and replaced it with the your new code but im getting
BASIC runtime error
Argument in not optional

and the arrow pointed to for i = 0 to len(r)

@acknak
thanks for the help, i dont know how to use it though, can i use that in bash? where can i use that?

If I had seen the shortcoming of my array formula at first, I wouldn't have posted it, and I wouldn't recommend spending time on it to anyone who hasn't had a good bit of experience with complicated expressions, unless they get some grasp of what I'm trying to do there. I would be happy to discuss it though.

I did subsequently try to refine it, and just when I thought I was approaching success, I pasted what I thought was going to be about the last crucial piece and got the error 512 (formula overflow). I suspect what I'm trying to do with that just can't be done in one cell.

My lettercodes function is a different story. To use that, don't replace Joiner with it, just put it into a module as an independent routine. You just use it like a regular spreadsheet function pointing to some Cell containing one of your codes, say A1:

Code: Select all

=LETTERCODES(A1)

And it should also work with

Code: Select all

=LETTERCODES("3339440-3392")

which indeed gives me CKCIDKJ-CKIB.

[acknak]

@acknak
thanks for the help, i dont know how to use it though, can i use that in bash? where can i use that?

You can use it on any computer with Perl installed, but I only meant to point out that the problem is much easier to solve with a different tool. It wasn't really offered as a practical solution.

[squenson]

i tried the formula of squenson and it was the best solution so far,

I am an

[VISS]

I've been looking for a while to do a similar job.
I need a formula to convert letters in to numbers (numeric alphabet / phone) although both the numbertext extension and the formula's listed here don't work

[acknak]

It might help if you could give us an example or two: what you put in and what you want to get out.

[VISS]

Sure,

When i try the formula's in this topic (and others)
I paste the formula into the cell in calc, i get 501 ERROR message.

Then i tried downloading the Number-Text formula and tried editing that to suite, it keeps translating numbers into words (literally)
All i want is that when i for example insert:
NA1BBK = 621225
According to numerical telephone alphabet

I've also tried:

Code: Select all

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"00","JK"),"11","AK"),"22","BK"),"33","CK"),"44","DK"),"55","EK"),"66","FK"),"77","GK"),"88","HK"),"99","IK"),"0","J"),"1","A"),"2","B"),"3","C"),"4","D"),"5","E"),"6","F"),"7","G"),"8","H"),"9","I")

[karolus]

Hallo
You want map the chars from Phone-keypad to their Numbers ? →

Code: Select all

Function phone_key_mapping( word ) as String

	oFunctionAccess = createUnoService( "com.sun.star.sheet.FunctionAccess" )
	map = array(array("_","a","d","g","j","m","p","t","w"))
	dim args(1) as variant
	args(1) =map
	number = ""
	for i = 1 to len( word )
		m = mid( word ,i,1)
		if instr("abcdefghijklmnopqrstuvwxyz", m) then
			args(0) = m 
			res =  oFunctionAccess.callFunction( "MATCH", args() )       
			number = number & res
		else
			number = number & m
		end if
	next i
      
	phone_key_mapping = number
	
end Function

edit: simplified the map's nestet array
Karolus

[squenson]

One of the reasons the formula may not work is that you are using a separator for arguments, in the formula, which is different from mine, the comma. I attach a file that should automatically adapt to your settings.

[karolus]

Hallo

@squenson:
VISS don't ask the same question as the OP.
He want convert for Example:
NA1BBK to 621225
Look at the keys of your mobile-phone and you will realize what it means.

( And if you want you can solve it with 26 nested SUBSTITUTE's )

Karolus

[squenson]

I know! My point was just to confirm that my formula works as it is. I always test my formulas before posting. I then let him tune it with the 26 SUBSTITUTE