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

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

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/

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

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

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