Hep with Find and Replace
 

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

Hep with Find and Replace
 

Hi Tom,

That's a bad data layout. You really should have one row per record,with a separate column for each field, thus:
Surname, Given Name, Initials, Addr Line 1, AddrLine 2, Locality, State, Country, Zip, Phone 1, Phone 2, etc
That way, you have complete discretion as to which parts of each record get merged (and where) in the document. The above layout
would, for example, allow you to put the initials in the addres block, but to personalise the body of the letter with the given
name. Some of the fields are options (eg Initials), depending on how rich your data are, and others can be left empty if there are
no data for them.

--
Cheers
macropod
[Microsoft MVP - Word]


"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

Hep with Find and Replace
 

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

Hep with Find and Replace
 

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

Hep with Find and Replace
 

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

Hep with Find and Replace
 

I meant to name the macro RemoveLastLineOfText.

--
Rick (MVP - Excel)


"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

Hep with Find and Replace
 

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

Hep with Find and Replace
 

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

Hep with Find and Replace
 

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