searching names for '*', replacing it with first name

[badi.baltgalvis]

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

 

[Bob Phillips]

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)

 

[Guest]

You could do this with a formula in B1,

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

 

[Bob Phillips]

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

--
HTH

Bob Phillips

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