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

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)

<[email protected]> wrote in message
news:[email protected]...
> 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
>

You could do this with a formula in B1,

=IF(ISERROR(FIND("*",A1)),A1,LEFT(A1,FIND(" ",A1)-1))

"[email protected]" 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
>
>

You can also use Data>Text To Columns with a * delimiter

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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)
>
> <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>