Hi Rick,

Your code chokes on cells without line-feeds. How about:

Sub RemoveLastLineOfText()
Dim C As Range, CVal As String
Dim Rng As Range, RngStart As String
RngStart = "B1"
With ActiveSheet
Set Rng = .Range(RngStart, _
.Range(RngStart).Offset(.Cells.SpecialCells(xlCell TypeLastCell).Row, 0).End(xlUp))
For Each C In Rng
CVal = C.Value
If InStrRev(CVal, vbLf) 0 Then
If Not CVal Like "* #####" And Not CVal Like "*#####-####" Then _
C.Value = Left(CVal, InStrRev(CVal, vbLf) - 1)
End If
Next
End With
End Sub

I've added code to process a nominated column (from a nominated starting row) automatically, without having to select anything.

--
Cheers
macropod
[Microsoft MVP - Word]


"Rick Rothstein" wrote in message ...
Okay then, here is a macro that doesn't assume there is always phone number
at the end, but it does assume there is a zip code at the end of the address
line though (that is what the If..Then is testing for)...

Sub RemoveLastLineOfText()
Dim C As Range
Dim CVal As String
For Each C In Selection
CVal = C.Value
If Not CVal Like "* #####" And Not CVal Like "*#####-####" Then _
C.Value = Left(CVal, InStrRev(CVal, vbLf) - 1)
Next
End Sub

--
Rick (MVP - Excel)


"macropod" wrote in message
...
Dangerous to assume!

--
Cheers
macropod
[Microsoft MVP - Word]


"Rick Rothstein" wrote in message
...
Assuming there is *always* a phone number in the cell, I think this macro
might be a better approach yet...

Sub LastLineOfText()
Dim C As Range
For Each C In Selection
C.Value = Left(C.Value, InStrRev(C.Value, vbLf) - 1)
Next
End Sub

--
Rick (MVP - Excel)


"macropod" wrote in message
...
Hi Don,

I think the following is a better approach:
Sub KillPhones()
Dim oCel As Range
Dim strVal As String
Dim i As Integer
With ActiveSheet.UsedRange
For Each oCel In .Columns(2).Cells
If InStr(oCel.Value, Chr(10)) 0 Then
strVal = Split(oCel.Value, Chr(10))(UBound(Split(oCel.Value,
Chr(10))))
i = Len(strVal)
strVal = Replace(Replace(Replace(Replace(strVal, "-", ""), " ",
""), "(", ""), ")", "")
i = i - Len(strVal) + 1
If i = 3 Then _
If IsNumeric(strVal) Then oCel.Value = Left(oCel.Value,
Len(oCel.Value) - Len(strVal) - i)
End If
Next
End With
End Sub

With this code, pretty well any phone number format comprising the last
line will be stripped out, but zip codes in any format will be left
intact.

--
Cheers
macropod
[Microsoft MVP - Word]


"Don Guillett" wrote in message
...
I strongly advise AGAINST posting in more than one group and every
couple of minutes. Bat netiquette. However, this should get you started
IF? you have a 5 number zip code. It finds the 1st number after an
arbitrary number of characters in the string and then strips out the
rest after 5 numbers.

Sub findnumberintext()
For Each c In Selection
For i = 6 To Len(c)
If IsNumeric(Mid(c, i, 1)) Then
ml = i
Exit For
End If
Next i
MsgBox Left(c, ml + 5)
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message
...
I have a spreadsheet in excel 2003. In cell a1 I have a name for
example:Smith,Lisa
In cell B2 I have an address and a phone number
for example
1234 Anywhere st
Akron,Ohio 44332
330-555-1212

I need to eliminate the phone number from the second field in order to
use
it as the basis for a mail merge
In other words I only want the street, city and state left without the
phone
number

I can't figure out how to do that successfully. The street
then the city state and zip
and finally the phone are on different lines in the same field

Can you put me in the right direction? Thanks so much