Text to Numbers

Just like that, each alphabet corresponds to a number, summing up alphabets

I cant really see how this post could be useful to you. One of my friends who believed in numerology brought this problem to me of converting names to numbers. Here is the solution that we worked out. You may treat this as a Just Like That entry until it finds any use.

Lets say the names are stored in column A, have worked out a numerical equivalent in column B using the equivalence (A or a =1) and (Z or z = 26) with all other alphabets in between

Method 1: Using CODE function

For windows CODE(text) returns the ANSI code of the first character in the text string.

• code(“A”)=65 and code(“Z”)=90 with all other upper case alphabets in between
• code(“a”)=97 and code(“z”)=122 with all other lower case alphabets in between

The formula in B2 {=SUM(CODE(MID(LOWER(A2),ROW(INDIRECT(“1:”&LEN(A2))),1))-96)}

entered as an array formula with ctrl+shift+enter

Here lower function is used so that all alphabets are treated as lower case and ansi code is from 97 to 122

Hence, 96 is subtracted so that a has numerical equivalent of 1

Cell A4 (Anup Agarwal) has space between the first name and last name. We can use substitute function to replace the space with no space. The revised formula in cell B4 is

B4 {=SUM(CODE(MID(LOWER(SUBSTITUTE(A4,” “,””)),ROW(INDIRECT(“1:”&LEN(SUBSTITUTE(A4,” “,””)))),1))-96)}

again entered as an array formula with ctrl+shift+enter

Method 2: Using lookup function

You can avoid using CODE function and use the lookup function to achieve the same result. Have defined the following table as equivalent

 

The formula entered in B2  {=SUM(LOOKUP(MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1),equivalent))}

entered as an array formula with ctrl+shift+enter

The formula entered in B4 is

{=SUM(LOOKUP(MID(SUBSTITUTE(A4,” “,””),ROW(INDIRECT(“1:”&LEN(SUBSTITUTE(A4,” “,””)))),1),equivalent))}

entered as an array formula with ctrl+shift+enter

Have fun and let me know if you ever find use of it 🙂

Read Full Post »

Converting numbers to words

How do we convert numbers to words. Especially useful if you are writing cheques (checks) through printing through a soft file.

The logic given below is one for India – it uses crores and lacs. The logic works for all numbers between 1 and 99,99,99,999 (100 crore -1)

Say the numbers that need to be converted to words are given in column A

Step 1: Start with defining the following arrays

Step2: Extract units, tens, hundreds, thousands, lacs and crores from the numbers in column A

The result is given Array1 below:

The formulas used are:

For crores: D2=ROUNDDOWN($A2,-7)

For lacs: E2=ROUNDDOWN($A2,-5)-$D2

For thousands: F2=ROUNDDOWN($A2,-3)-SUM($D2:E2)

For hundreds: G2=ROUNDDOWN($A2,-2)-SUM($D2:F2)

For tens & units: H2=$A2-SUM($D2:G2)

Drag till end of columns

Modify the Array1 so that resuts appear as this in Array2:

The formulas used are:

For crores: I2=D2/10^7

For lacs: J2=E2/10^5

For thousands: K2=F2/10^3

For hundreds: L2=G2/10^2

For tens+units: M2=H2

Step 3: Convert numbers to words for each column

The formulas used are:

Crores: N2=IF(LEN(I2)=1,VLOOKUP(I2,Unit_array,2,FALSE),IF(VALUE(LEFT(I2,1))=1,VLOOKUP(I2,teens,2,FALSE),CONCATENATE(VLOOKUP(VALUE(LEFT(I2,1)),tens,2,FALSE),” “,VLOOKUP(VALUE(RIGHT(I2,1)),Unit_array,2,FALSE))))

Lacs: O2=IF(LEN(J2)=1,VLOOKUP(J2,Unit_array,2,FALSE),IF(VALUE(LEFT(J2,1))=1,VLOOKUP(J2,teens,2,FALSE),CONCATENATE(VLOOKUP(VALUE(LEFT(J2,1)),tens,2,FALSE),” “,VLOOKUP(VALUE(RIGHT(J2,1)),Unit_array,2,FALSE))))

Thousands: P2=IF(LEN(K2)=1,VLOOKUP(K2,Unit_array,2,FALSE),IF(VALUE(LEFT(K2,1))=1,VLOOKUP(K2,teens,2,FALSE),CONCATENATE(VLOOKUP(VALUE(LEFT(K2,1)),tens,2,FALSE),” “,VLOOKUP(VALUE(RIGHT(K2,1)),Unit_array,2,FALSE))))

Hundreds: Q2=VLOOKUP(L2,Unit_array,2,FALSE)

Tens&Units: R2=IF(LEN(M2)=1,VLOOKUP(M2,Unit_array,2,FALSE),IF(VALUE(LEFT(M2,1))=1,VLOOKUP(M2,teens,2,FALSE),CONCATENATE(VLOOKUP(VALUE(LEFT(M2,1)),tens,2,FALSE),” “,VLOOKUP(VALUE(RIGHT(M2,1)),Unit_array,2,FALSE))))

Formula for crores, lacs, thousands, tens&units are similar in logic and construct

The logic used is

• If number is of 1 digit (0 to 9)- then lookup value from Unit_array
• If number starts with 1 (10 to 19) – then lookup value from teens
• For all else (20 to 99) – lookup left digit from tens and right digit from Unit_array and concatenate

Formula for hundreds is simple – lookup value from Unit_array as hundreds can only be from 0 to 9

Step 4: Now the fun part – putting it all together

The formula is simply

B2=PROPER(CONCATENATE(IF(I2>0,N2&” crores “,””),IF(J2>0,O2&” lacs “,””),IF(K2>0,P2&” thousand “,””),IF(L2>0,Q2&” hundred “,””),IF(R2>0,R2,””)))

Have added proper to capitalize the first letters of all words in the text string.

Let me know if it helps you 🙂

Read Full Post »

Remove spaces from a cell

Often when a list is imported from another application, you would find that there are unwanted spaces within cells.
If there are spaces in a numeric cell, excel reads it as text field. This means no calculation can be carried out on them.
For instance if a cell contains 1234 1 (space between 4 and 1), this becomes a text field and no arithmetic operation can be carried out. If you add 1 to the cell it displays #VALUE! error.

We would make use of two excel functions to get rid of unwanted spaces

TRIM and SUBSTITUTE

TRIM function removes spaces from

• before the start of text eg ” 123″
• after the end of text eg “123 “
• if there are more than one spaces in the middle of the text eg. “1 23” (there are two spaces between 1 and 2, trim would remove one of them)

Cell B12 shows the use of Substitute function. The function replaces all spaces ” ” with no space “”

ps: you can find the length of a string using LEN function. The function has been used in column C. For instance C2 contains the function LEN(B2)

Read Full Post »

Separating names into last and first names

I had encountered this problem while carrying out mail merge. Usually names are stored in a single field. In order to carry out mail merge for a letter/mailer, last names needs to be stored in a separate field to faciliate merge as Dear Mr/Ms last_name

The original list is stored in column A whereas the desired lists in columns B and C.
Formula used in cell B2 is =RIGHT(A2,LEN(A2)-FIND(” “,A2))
Formula used in cell C2 is =LEFT(A2,FIND(” “,A2)-1)
Dragging these formulas give us the desired results in columns B and C

Alternative solution

1. Select A2:A8

2. From toolbar –>Data –> Text to Columns… –>Delimited–>Space–> Finish

Make sure that column B has no data to begin with. If there is some data already, insert a blank column B.

Appears as First Name, Last Name in columns A and B

Read Full Post »