replace letters for numbers
 

I have a big list of names that i need to replace for numbers .... is there
any way to do this in excel?
example:

Eduardo Gomez 3382736 46639 (according telephone buttons numbers)

Anny help.....very appreciated! Thanks for your time.
--
Teresa

replace letters for numbers
 

If you have a translation table that gives a number for each name, then you
can use VLOOKUP().

See:

http://support.microsoft.com/default.aspx/kb/181213
--
Gary''s Student - gsnu200856


"Maria Teresa" wrote:

I have a big list of names that i need to replace for numbers .... is there
any way to do this in excel?
example:

Eduardo Gomez 3382736 46639 (according telephone buttons numbers)

Anny help.....very appreciated! Thanks for your time.
--
Teresa

replace letters for numbers
 

Gary, she is looking to replace each letter for a number, how vlookup will
look for each one?

thanks

--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Gary''s Student" escreveu:

If you have a translation table that gives a number for each name, then you
can use VLOOKUP().

See:

http://support.microsoft.com/default.aspx/kb/181213
--
Gary''s Student - gsnu200856


"Maria Teresa" wrote:

I have a big list of names that i need to replace for numbers .... is there
any way to do this in excel?
example:

Eduardo Gomez 3382736 46639 (according telephone buttons numbers)

Anny help.....very appreciated! Thanks for your time.
--
Teresa

replace letters for numbers
 

Say we have a list of names in column A from A1 thru A2000. The names all
appear in a much smaller list from C1 thru C50. In D1 thru D50 are the
equivalend numbers for each name.

In B1 enter:
=VLOOKUP(A1,$C$1:$D50,2,FALSE) and copy down thru B2000

Then copy column B and Paste/Special/Values onto column A.

Finally column B can be cleared.
--
Gary''s Student - gsnu200856


"Marcelo" wrote:

Gary, she is looking to replace each letter for a number, how vlookup will
look for each one?

thanks

--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Gary''s Student" escreveu:

If you have a translation table that gives a number for each name, then you
can use VLOOKUP().

See:

http://support.microsoft.com/default.aspx/kb/181213
--
Gary''s Student - gsnu200856


"Maria Teresa" wrote:

I have a big list of names that i need to replace for numbers .... is there
any way to do this in excel?
example:

Eduardo Gomez 3382736 46639 (according telephone buttons numbers)

Anny help.....very appreciated! Thanks for your time.
--
Teresa

replace letters for numbers
 

I would use an extra column and a user defined function that converted the words
to digits. If I didn't want the original names, I could convert these cells
with formulas to values and delete the original values.

If that sounds reasonable to you:

Option Explicit
Function ConvertToTel(myStr As String) As String

Dim iCtr As Long
Dim myNum As Long

myNum = -1
For iCtr = Asc("A") To Asc("Z")
Select Case iCtr
Case Asc("A") To Asc("C"): myNum = 2
Case Asc("D") To Asc("F"): myNum = 3
Case Asc("G") To Asc("I"): myNum = 4
Case Asc("J") To Asc("L"): myNum = 5
Case Asc("M") To Asc("O"): myNum = 6
Case Asc("P") To Asc("S"): myNum = 7
Case Asc("T") To Asc("V"): myNum = 8
Case Asc("W") To Asc("Z"): myNum = 9
End Select
If myNum = -1 Then
'skip this character
Else
myStr = Replace(myStr, Chr(iCtr), myNum, 1, -1, vbTextCompare)
End If
Next iCtr

ConvertToTel = myStr

End Function

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

========
Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Then into a test cell and type this in B1:
=ConvertToTel(a1)
Where A1 contains the name.


Maria Teresa wrote:

I have a big list of names that i need to replace for numbers .... is there
any way to do this in excel?
example:

Eduardo Gomez 3382736 46639 (according telephone buttons numbers)

Anny help.....very appreciated! Thanks for your time.
--
Teresa

--

Dave Peterson

replace letters for numbers
 

what about a coide to chage all "a' to number 2 and "z" to number 9?

like that one that right the currencys?

$100 - one hundred dollars

thanks man

--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Gary''s Student" escreveu:

Say we have a list of names in column A from A1 thru A2000. The names all
appear in a much smaller list from C1 thru C50. In D1 thru D50 are the
equivalend numbers for each name.

In B1 enter:
=VLOOKUP(A1,$C$1:$D50,2,FALSE) and copy down thru B2000

Then copy column B and Paste/Special/Values onto column A.

Finally column B can be cleared.
--
Gary''s Student - gsnu200856


"Marcelo" wrote:

Gary, she is looking to replace each letter for a number, how vlookup will
look for each one?

thanks

--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Gary''s Student" escreveu:

If you have a translation table that gives a number for each name, then you
can use VLOOKUP().

See:

http://support.microsoft.com/default.aspx/kb/181213
--
Gary''s Student - gsnu200856


"Maria Teresa" wrote:

I have a big list of names that i need to replace for numbers .... is there
any way to do this in excel?
example:

Eduardo Gomez 3382736 46639 (according telephone buttons numbers)

Anny help.....very appreciated! Thanks for your time.
--
Teresa