Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find & Replace: find part cell, replace whole cell | Excel Worksheet Functions | |||
Find and Replace - Replace with Blank Space | Excel Discussion (Misc queries) | |||
where to put results of find operation in find and replace functio | Excel Worksheet Functions | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
find replace cursor default to find box | Excel Discussion (Misc queries) |