Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting the number in the first line of the address in a cell
I have spent the last few hours trying to find out haw to delete
numbers in the first line of the address in excel, by using a macro or formula. For example In a particular cell I would have 35 Mill View Road and I want to strip the numbers just to leave Mill View Road. I'm a Novice when programming in Excel. Hope somebody can help me, Thanks in advance Regards Andy P |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting the number in the first line of the address in a cell
Untested:
I = instr(activecell.value," ") if I 0 then activecell.value = mid(activecell.value, I+1) End if Instr returns the starting location of a string, in this case a space. Mid, returns the text starting at the I+1 character postion. Robert Flanagan Macro Systems Delaware, U.S. 302-234-9857 http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "andy peo" wrote in message ... I have spent the last few hours trying to find out haw to delete numbers in the first line of the address in excel, by using a macro or formula. For example In a particular cell I would have 35 Mill View Road and I want to strip the numbers just to leave Mill View Road. I'm a Novice when programming in Excel. Hope somebody can help me, Thanks in advance Regards Andy P |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting the number in the first line of the address in a cell
do the numbers always go at the beginning of the address, such as:
35 melrose place and you need melrose place or you may have 1) C/ san jeronimo 11, 5B (28010) madrid 2) 28 oxford Strees, 54W-47 and you need C/ san jeronimo , B () madrid oxford Strees, W- --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting the number in the first line of the address in a cell
I like the second one. ;-)
Function NoNums(s As String) As String Dim i As Integer Dim buf As String Dim char As String For i = 1 To Len(s) char = Mid(s, i, 1) If Not IsNumeric(char) Then buf = buf & char End If Next i NoNums = buf End Function Function NoNums2(s As String) As String NoNums2 = _ Replace( _ Replace( _ Replace( _ Replace( _ Replace( _ Replace( _ Replace( _ Replace( _ Replace( _ Replace( _ s, 1, "") _ , 2, "") _ , 3, "") _ , 4, "") _ , 5, "") _ , 6, "") _ , 7, "") _ , 8, "") _ , 9, "") _ , 0, "") End Function "andy peo" wrote in message ... I have spent the last few hours trying to find out haw to delete numbers in the first line of the address in excel, by using a macro or formula. For example In a particular cell I would have 35 Mill View Road and I want to strip the numbers just to leave Mill View Road. I'm a Novice when programming in Excel. Hope somebody can help me, Thanks in advance Regards Andy P |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting the number in the first line of the address in a cell
I feel a bit stupid asking this question but i have tried.
How would you get this function NoNums in to excel and to run it in the worksheet Thanks in advance Regards Andy P "Bob Kilmer" wrote in message ... I like the second one. ;-) Function NoNums(s As String) As String Dim i As Integer Dim buf As String Dim char As String For i = 1 To Len(s) char = Mid(s, i, 1) If Not IsNumeric(char) Then buf = buf & char End If Next i NoNums = buf End Function Function NoNums2(s As String) As String NoNums2 = _ Replace( _ Replace( _ Replace( _ Replace( _ Replace( _ Replace( _ Replace( _ Replace( _ Replace( _ Replace( _ s, 1, "") _ , 2, "") _ , 3, "") _ , 4, "") _ , 5, "") _ , 6, "") _ , 7, "") _ , 8, "") _ , 9, "") _ , 0, "") End Function "andy peo" wrote in message ... I have spent the last few hours trying to find out haw to delete numbers in the first line of the address in excel, by using a macro or formula. For example In a particular cell I would have 35 Mill View Road and I want to strip the numbers just to leave Mill View Road. I'm a Novice when programming in Excel. Hope somebody can help me, Thanks in advance Regards Andy P |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting the number in the first line of the address in a cell
David McRitchie has an intro to macros at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm 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. Into a test cell and type: =nonums(a1) === And a word of warning--if you're using xl97, change "replace" to "application.substitute" (all of them). (Replace was added in xl2k.) andy peo wrote: I feel a bit stupid asking this question but i have tried. How would you get this function NoNums in to excel and to run it in the worksheet Thanks in advance Regards Andy P "Bob Kilmer" wrote in message ... I like the second one. ;-) Function NoNums(s As String) As String Dim i As Integer Dim buf As String Dim char As String For i = 1 To Len(s) char = Mid(s, i, 1) If Not IsNumeric(char) Then buf = buf & char End If Next i NoNums = buf End Function Function NoNums2(s As String) As String NoNums2 = _ Replace( _ Replace( _ Replace( _ Replace( _ Replace( _ Replace( _ Replace( _ Replace( _ Replace( _ Replace( _ s, 1, "") _ , 2, "") _ , 3, "") _ , 4, "") _ , 5, "") _ , 6, "") _ , 7, "") _ , 8, "") _ , 9, "") _ , 0, "") End Function "andy peo" wrote in message ... I have spent the last few hours trying to find out haw to delete numbers in the first line of the address in excel, by using a macro or formula. For example In a particular cell I would have 35 Mill View Road and I want to strip the numbers just to leave Mill View Road. I'm a Novice when programming in Excel. Hope somebody can help me, Thanks in advance Regards Andy P -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deleting the first 2 digits of number in a cell | Excel Worksheet Functions | |||
Insertion of a cell address that constantly rises by a set number | Excel Discussion (Misc queries) | |||
Find max number of character and return cell address | Excel Worksheet Functions | |||
How do I include cell data in subject line of email address/hyperl | Excel Worksheet Functions | |||
Number format as condition for not deleting a line | Excel Programming |