Question regarding excel sheet

[Zach09]

To all Excel Gurus here,

 

How do you assign an alphabet to a number?

Example:

A=1

B=2

C=3

 

Until Z..

 

So when I enter 10K it will show 111.

 

Do i need to enter code in VBA? Are there any functions to do this?

 

Thanks in advance..

 

 

[Darryn]

To all Excel Gurus here,

 

How do you assign an alphabet to a number?

Example:

A=1

B=2

C=3

 

Until Z..

 

So when I enter 10K it will show 111.

 

Do i need to enter code in VBA? Are there any functions to do this?

 

Thanks in advance..

Tink can use an "if / then" function, but not sure if can nest 26...

 

[Xbeano]

Not sure how you want it to arrive at 111, based on my limited knowledge you can use VLOOKUP function.

 

See attached sheet. But I could not get your 111. I got 1011 instead

Book1.xls

[Ladykillerz]

not sure what you really mean.

 

try =HEX2DEC(A1) from alphabet to number ; =DEC2HEX(A1) from number to alphabet.

 

the value must be at A1.

[XEvolutioNX]

by the way i want to ask why my excel can only calculate number without comma... with comma the sum will be 0

[Ladykillerz]

by the way i want to ask why my excel can only calculate number without comma... with comma the sum will be 0

 

should not be 0. instead #VALUE!.

or did i misinterpreted your qns?

 

do give example(s) so that we can understand your qns better.

[Japkoi]

if you could separate the 10 and K into 2 separate cells, eg. 100 & K in cells A1 and B1 respectively

 

you could perform a a vlookup on cell B1 with C1, then perform = A1 + C1 to get 111

[Ifx23686]

To all Excel Gurus here,

 

How do you assign an alphabet to a number?

Example:

A=1

B=2

C=3

 

Until Z..

 

So when I enter 10K it will show 111.

 

Do i need to enter code in VBA? Are there any functions to do this?

 

Thanks in advance..

 

err... i thought you want to assign alphabet to number, but i dont understand how 10k = 111 comes into play.

 

i can help if objective is clearer. trust me there 101 ways to do something as long as its logical.

[Zach09]

Thanks for the replies so far.

 

To explain how I derive 10K = 111;

 

1st digit = 1

K = 11. As it is the 11th digit in alphabetical order.

 

My objective is to use only 3 digits.

 

To give some background on why I ask the question above:

 

I have 10 marbles individually labelled A-Z

I also have 3 sets of these marbles.

 

So by assigning the 3 digits I will straightaway know each marble come from which set and position in their respective set. I hope I'm clear enough.

[XEvolutioNX]

should not be 0. instead #VALUE!.

or did i misinterpreted your qns?

 

do give example(s) so that we can understand your qns better.

when i type 1,000 and 2,000 for example the sum that i get from excel is 0

but without the comma is ok.... why is it like that?..... and anything i can do to let it be ok

[Ultramega]

Thanks for the replies so far.

 

To explain how I derive 10K = 111;

 

1st digit = 1

K = 11. As it is the 11th digit in alphabetical order.

 

My objective is to use only 3 digits.

 

To give some background on why I ask the question above:

 

I have 10 marbles individually labelled A-Z

I also have 3 sets of these marbles.

 

So by assigning the 3 digits I will straightaway know each marble come from which set and position in their respective set. I hope I'm clear enough.

 

Assume Cell A1 contains "10K"

 

Use LEFT(A1,1) to pick up the "1" (pick it up cos you want to keep it).

 

Use RIGHT (A1,1) to pick up the "K" (pick it up cos u want to convert it).

 

Use IF function to assign number to alphabet, eg, IF(RIGHT(A1,1)="A","01",IF(RIGHT(A1,1)="B","02",IF(RIGHT(A1,1)="K","11","ZZZ")))

 

What this does is this: if the alphabet is "A", display "01" (and the formula ends there), if not then check if it's "B" and if so display "02" (and formula ends there), if not then check if it's "K" and if so display "11", if not then display "ZZZ". You need to keep doing this till "Y". "ZZZ" is the number for Z cos when u reach that part of the formula it means the alphabet is not A-Y so it can only be Z. Sorry i know this is not clear but is the best i can explain.

 

Once done use "&" to join them up, eg:

 

=LEFT(A1,1)&IF(RIGHT(A1,1)="A","01",IF(RIGHT(A1,1)="B","02",IF(RIGHT(A1,1)="K","11","ZZZ")))

 

 

Press F1 button when in Excel, search and read more about these 3 functions.

 

Btw, this is lousy method hor. There should be better ways to do this, like using another sheet as reference table... anyone?

Edited September 22, 2010 by Ultramega

[Passion]

when i type 1,000 and 2,000 for example the sum that i get from excel is 0

but without the comma is ok.... why is it like that?..... and anything i can do to let it be ok

Let me explain. when you key 1000,it's a number.But when you put 1,000,it became a string.How to add a string?You can only combine a string,not add a string.

[Passion]

To all Excel Gurus here,

 

How do you assign an alphabet to a number?

Example:

A=1

B=2

C=3

 

Until Z..

 

So when I enter 10K it will show 111.

 

Do i need to enter code in VBA? Are there any functions to do this?

 

Thanks in advance..

Can I ask,if 10C = 103? 10Z = 126 or 1026?

[Zach09]

Hi,

 

10Z should be 126..

 

Rgds

[Zach09]

Assume Cell A1 contains "10K"

 

Use LEFT(A1,1) to pick up the "1" (pick it up cos you want to keep it).

 

Use RIGHT (A1,1) to pick up the "K" (pick it up cos u want to convert it).

 

Use IF function to assign number to alphabet, eg, IF(RIGHT(A1,1)="A","01",IF(RIGHT(A1,1)="B","02",IF(RIGHT(A1,1)="K","11","ZZZ")))

 

What this does is this: if the alphabet is "A", display "01" (and the formula ends there), if not then check if it's "B" and if so display "02" (and formula ends there), if not then check if it's "K" and if so display "11", if not then display "ZZZ". You need to keep doing this till "Y". "ZZZ" is the number for Z cos when u reach that part of the formula it means the alphabet is not A-Y so it can only be Z. Sorry i know this is not clear but is the best i can explain.

 

Once done use "&" to join them up, eg:

 

=LEFT(A1,1)&IF(RIGHT(A1,1)="A","01",IF(RIGHT(A1,1)="B","02",IF(RIGHT(A1,1)="K","11","ZZZ")))

 

 

Press F1 button when in Excel, search and read more about these 3 functions.

 

Btw, this is lousy method hor. There should be better ways to do this, like using another sheet as reference table... anyone?

 

Your method is a bit tedious but nevertheless I'll give it a try..

Thanks for your suggestion

 

 

[Passion]

from ts view,my propose solution is this. a = 1,b =2,c=3 etc,put it in another spreadsheet.

 

create a cell to allow user input,let's call it cell A2

 

Use cell A3 to determine the length by using this, =len(a2)

 

Read the last alphanumeric from the cell by using this formula =right(A2,1) in cell A4

 

It should retrieve either A to Z

 

Next use cell B2 to read from cell A4 to determine the alphabet,it should show a value by using this formula =vlookup(A3,spreadsheet name,column to look up for,false)

 

This should return you a figure, say for example,if A3 = C,it should return 3.

 

Now,lets use A5 to do this formula. =left(A2,a3-1),it should return the figure user input without the alphabet

 

Now concantenate A5 with A4.Simply use this, A5 & A4. you should get the result. Of course,this is just my assumption.

[Passion]

sorry TS,my view on if k= 11. and you want 10k = 111. I don't see how the formula going to do the trick as it's a static programming.What I've type earlier on is more of object oriented than static.

 

why i say that,because if user key in 12k,what should be the result?It cannot be done,and also not realistic.But of course,unless you lock down the input field with only 10A,10B to 10Z.Sorry but just my 2 cents worth.

Edited September 22, 2010 by Passion

[XEvolutioNX]

Let me explain. when you key 1000,it's a number.But when you put 1,000,it became a string.How to add a string?You can only combine a string,not add a string.

 

how do i enable my excel to accept string of number as my work require me to calculate a lump of number........ thans in advance