Hep with Find and Replace

T

Tom

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
 
M

macropod

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.
 
D

Don Guillett

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
 
M

macropod

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.
 
R

Rick Rothstein

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 said:
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 said:
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
(e-mail address removed)
Click to expand...
Click to expand...
 
R

Rick Rothstein

I meant to name the macro RemoveLastLineOfText.

--
Rick (MVP - Excel)


Rick Rothstein said:
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 said:
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 said:
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
(e-mail address removed)
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
Click to expand...
Click to expand...
Click to expand...
 
M

macropod

Dangerous to assume!

--
Cheers
macropod
[Microsoft MVP - Word]


Rick Rothstein said:
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 said:
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 said:
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
(e-mail address removed)
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
Click to expand...
Click to expand...
Click to expand...
 
R

Rick Rothstein

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 said:
Dangerous to assume!

--
Cheers
macropod
[Microsoft MVP - Word]


Rick Rothstein said:
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 said:
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]


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
(e-mail address removed)
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
Click to expand...
Click to expand...
Click to expand...
 
M

macropod

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(xlCellTypeLastCell).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 said:
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 said:
Dangerous to assume!

--
Cheers
macropod
[Microsoft MVP - Word]


Rick Rothstein said:
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)


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]


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
(e-mail address removed)
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

finding and replacing text with a blank 2
Replacing Text in a cell with blanks 2
Address Format changed after Migration 0
Replace hard return with a delimiter? 3
Splitting Text from single cell in column across multiple Columns 3
Create a macro to eliminate certain words from several columns 4
Need to merge columns 10
Word macro that can call cells out of Excel for a find and replace 0

Top