Convert numbers to letters and letters to numbers A=1, B=2 etc

Question

Convert numbers to letters and letters to numbers A=1, B=2 etc

There is a formula here http://www.ozgrid.com/forum/showthread.php?t=64360 for Excel, I'm interested in how to do the original answer posted on numbers for iOS

All I want is to be able to do is input names and get a numerical output of the name for example Michael = 13 9 3 8 1 5 12 & Totals 51.

If anyone can clearly explain how I would be able to do this on numbers I would be very appreciative.

This was the answer for excel but it doesn't work and I can't find a solution for iOS.

for an upper case alphabet use the formula =CODE(A2)-64 that will give A as 1, B as 2 and so on

for a lower case alphabet use the formula =CODE(A2)-96 that will give a as 1, b as 2 and so on

where the alphabet is in cell A2

Or, right click on the sheet name tab, choose View Code and modify to suit.

VB:

'Remove for 0=a Option Base 1 '1=a Private Sub Worksheet_Change(ByVal Target As Range) Dim MyArray Dim lloop As Long MyArray = Array("a", "b", "c", "d", "e", "f", "g", _ "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", _ "r", "s", "t", "u", "v", "w", "x", "y", "z") With Target(1, 1) If .Column = 3 Then 'Column C Only If IsNumeric(.Value) Then If .Value >= LBound(MyArray) And .Value <= UBound(MyArray) Then Target(1, 1).NumberFormat = """" & UCase(MyArray(Target(1, 1))) & """" End If End If End If End With End Sub

iPad Air, iOS 8.4

Posted on Jul 24, 2015 11:46 AM

Reply

Answer 1

Mar 6, 2017 6:10 PM in response to t quinn

Cool, I did it, your a legend T Quinn, I couldn't have done it without your help...I wouldn't have known how to figure that out, I only know what I wanted and you provided the solution. thanks heaps:-)

Reply

Answer 2

Mar 6, 2017 6:10 PM in response to t quinn

Your cool T, I managed to alter the code as you suggested and it shows up as an empty cell, however it still doesn't calculate the totals correctly.

Its not a major concern, it's just for ease of use, minimising the amount of information I have to input. I like it over two lines for displaying the information since I can clearly see which values correspond to which letter. That's my favourite part of your original layout, it makes it perfect for what I want it for.

I Guess all I really need to know now is if its possible to give a blank cell the value 0 so it calculates the totals correctly.

edit: Done I just placed a zero between the "0" like so and wha la

Reply

Answer 3

Mar 6, 2017 6:10 PM in response to t quinn

It works a charm T Quinn, I just placed a zero between the "0" like so and its calculating perfectly:-D

GIves a blank cell with a zero value.

Edit. The formula for L3 was simply L4 + L5 + L6, etc, etc though because I had it calculating the seventh cell and it had no value it was reporting an error

Reply

Answer 4

Mar 6, 2017 6:10 PM in response to t quinn

It looks to be axactly what I'm after and a perfect solution but I still don't know how to do it? It's my first time using numbers and I think I need a little more explanation on how to do it, I played around with it a little and found the lower and mid functions but I still can't get it to work exactly how you did it although that is correct for what I want to know how to do. I'll try play around a bit more and see if I can figure it out, Thanks for your help T Quinn.

Reply

Answer 5

Mar 6, 2017 6:10 PM in response to t quinn

I'll try that then, I'd rather it done properly. Thank you very much for all your help T Quinn:-)

Oh and it wasn't L4 it was SUM (D12 + E12 + F12 +G12 + H12 + I12 + J12) however J12 was not giving a value.

Reply

Answer 6

Mar 6, 2017 6:10 PM in response to Community User

I'm doing something wrong?

Sorry T Quinn I'm very new to this and I can't work out how to replicate your answer.

Reply

Answer 7

Mar 6, 2017 6:10 PM in response to Community User

One last question, what is equivalent to zero so I can program it for longer and shorter names while still giving me the correct totals Instead of the exclamation mark?

Reply

Answer 8

t quinn

Level 5

7,043 points

Aug 1, 2016 8:34 AM in response to Community User

Hi MAC Pickles,

Here is an approach that uses a cell for each letter in the name. If your maximum name length is 10 then yuo will need 10 columns in addition to the first.

LOWER() converts the output to lower case. MID() uses the column number to determine which letter to return.

Here is your CODE() function.

A3 is a SUM of the rest of the row.

quinn

Reply

Answer 9

t quinn

Level 5

7,043 points

Aug 1, 2016 8:34 AM in response to Community User

Hi MAC,

When you are building a formula it is important to tap things is the right order. C3 should be your source-string in MID() and the whole function MID() is the source-string for LOWER(). Tap the ℹ to get an explanation of MID() in your function browser. Check out the differences in our formulas.

If you commit to inputing all lower case letters in your names you can do without LOWER(). The basic formula is this:

=MID($C3,1,1).

this will grab the first letter in C3. It will return a capital letter if it is there and that will cause a problem with CODE() later. I took care of that by wrapping in in LOWER().

=MID($C3,2,1) This will grab the second letter. See how it works?

Since I don't want to adjust the formula for each letter I used COLOMN(cell) in place of the start-pos value. In column D (forth column) I would need to subtract 3 to get a 1.

=(MID($C3,COLOMN(cell)-3,1).

The $ in $C3 tells Numbers to keep the column the same when we fill this formula across. You will get this on your iPad by tapping the down arrow in the C3 lozenge ( I never know what ot call those) and choosing "preserve column".

Once you have the formula working in D3 you can fill it across. Tap the cell till you get the cut/copy/paste menu and use the arrow to get "fill". Grab the right side of the yellow box and fill the rest of your columns.

Let me know how it goes. We will be making further tweaks.

quinn

As a further note on the construction of your table, I would be inclined to put everything relating to a single name on one row if I was going to have multiple names.

I would then hide everything but the first 2 columns.

q

Reply

Answer 10

t quinn

Level 5

7,043 points

Aug 1, 2016 8:35 AM in response to Community User

Hi MAC,

Good job! Check out my suggestion for streamlining the presentation.

But first, See what happens when you enter "Thomas" or "Bill".

We need to modify the Code formula so it doesn't throw an error. One way to do this is to wrap the formula in IFERROR(). I have gotten into the habit of testing the cell if it blank in situations like this. LEN() counts the characters.