Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace the column letters with my own heading | Charts and Charting in Excel | |||
How to replace numeric values with letters in a diagram? | Excel Discussion (Misc queries) | |||
Sorting - cells containing numbers, numbers and letters | Excel Discussion (Misc queries) | |||
create self-generating numbers with letters and numbers | Excel Discussion (Misc queries) | |||
Looking for an easy way to replace letters with acutes | Excel Worksheet Functions |