Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching names for '*', replacing it with first name
Hello:
I have an Excel workbook with the first column of Customer Name. This column contains the names of both businesses and personal customers. Personal customers have the '*' symbol in front of their last name (i.e. ROBERT A *SMITH). I would like to create a macro that scans each customer name for an asterisk - if one exists, take the personal customer's first name and place it in an adjacent column. I plan to use this new column as the greeting in a mail merge. If there is no asterisk in a customer name then it is a business and the entire business name would be used in the greeting. Example: Robert A *Smith in cell A1 would be changed to Robert in cell B1...for "Dear Robert:" Picture Perfect Inc in cell A1 would be Picture Perfect Inc in cell B1...for "Dear Picture Perfect Inc:" Having this in a macro would be ideal because it could run at the end of a series of macros I have that run in sucession. Thanks in advance! -Badi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching names for '*', replacing it with first name
Sub Test()
Dim iLastRow As Long Dim i As Long Dim iPos As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To iLastRow iPos = InStr(Cells(i, "A").Value, "*") If iPos 0 Then Cells(i, "B").Value = Right(Cells(i, "A").Value, _ Len(Cells(i, "A").Value) - iPos) Cells(i, "A").Value = Left(Cells(i, "A").Value, iPos - 1) End If Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ups.com... Hello: I have an Excel workbook with the first column of Customer Name. This column contains the names of both businesses and personal customers. Personal customers have the '*' symbol in front of their last name (i.e. ROBERT A *SMITH). I would like to create a macro that scans each customer name for an asterisk - if one exists, take the personal customer's first name and place it in an adjacent column. I plan to use this new column as the greeting in a mail merge. If there is no asterisk in a customer name then it is a business and the entire business name would be used in the greeting. Example: Robert A *Smith in cell A1 would be changed to Robert in cell B1...for "Dear Robert:" Picture Perfect Inc in cell A1 would be Picture Perfect Inc in cell B1...for "Dear Picture Perfect Inc:" Having this in a macro would be ideal because it could run at the end of a series of macros I have that run in sucession. Thanks in advance! -Badi |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching names for '*', replacing it with first name
You could do this with a formula in B1,
=IF(ISERROR(FIND("*",A1)),A1,LEFT(A1,FIND(" ",A1)-1)) " wrote: Hello: I have an Excel workbook with the first column of Customer Name. This column contains the names of both businesses and personal customers. Personal customers have the '*' symbol in front of their last name (i.e. ROBERT A *SMITH). I would like to create a macro that scans each customer name for an asterisk - if one exists, take the personal customer's first name and place it in an adjacent column. I plan to use this new column as the greeting in a mail merge. If there is no asterisk in a customer name then it is a business and the entire business name would be used in the greeting. Example: Robert A *Smith in cell A1 would be changed to Robert in cell B1...for "Dear Robert:" Picture Perfect Inc in cell A1 would be Picture Perfect Inc in cell B1...for "Dear Picture Perfect Inc:" Having this in a macro would be ideal because it could run at the end of a series of macros I have that run in sucession. Thanks in advance! -Badi |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching names for '*', replacing it with first name
You can also use DataText To Columns with a * delimiter
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Bob Phillips" wrote in message ... Sub Test() Dim iLastRow As Long Dim i As Long Dim iPos As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To iLastRow iPos = InStr(Cells(i, "A").Value, "*") If iPos 0 Then Cells(i, "B").Value = Right(Cells(i, "A").Value, _ Len(Cells(i, "A").Value) - iPos) Cells(i, "A").Value = Left(Cells(i, "A").Value, iPos - 1) End If Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ups.com... Hello: I have an Excel workbook with the first column of Customer Name. This column contains the names of both businesses and personal customers. Personal customers have the '*' symbol in front of their last name (i.e. ROBERT A *SMITH). I would like to create a macro that scans each customer name for an asterisk - if one exists, take the personal customer's first name and place it in an adjacent column. I plan to use this new column as the greeting in a mail merge. If there is no asterisk in a customer name then it is a business and the entire business name would be used in the greeting. Example: Robert A *Smith in cell A1 would be changed to Robert in cell B1...for "Dear Robert:" Picture Perfect Inc in cell A1 would be Picture Perfect Inc in cell B1...for "Dear Picture Perfect Inc:" Having this in a macro would be ideal because it could run at the end of a series of macros I have that run in sucession. Thanks in advance! -Badi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replacing a list of codes with names | Excel Worksheet Functions | |||
Replacing A,B,C column names to other names | Excel Discussion (Misc queries) | |||
Replacing Range Names | Excel Discussion (Misc queries) | |||
Cell searching and Replacing | Excel Discussion (Misc queries) | |||
replacing just the sheet names within formulas | Excel Worksheet Functions |