I?d like assistance with writing a macro that will parse a text string
contained in a ?Name? field. The text should be parsed into new cells
in proper case format, as follows:

-Business Name
-First and Middle Name
-Last Name
-Credentials

Some of the text that is currently in the ?Name? cells are names of
physicians and other healthcare professionals. The other entries are
business names. The only unique features I could find to differentiate
physicians? names from business names are commas: NONE of the business
names contain commas but ALL of the physician names do contain commas.
I don?t know if that helps or not.

Here are some examples of some of the entries that are currently in
the ?Name? field with how I would like for the macro to parse them:

Current: 
ALLIANCE REHABILITATION LLC
Parsed: (do not parse- business name). Copy text into Business Name column.
Business Name: Alliance Rehabilitation LLC
First and Middle Name: null
Last Name: null
Credentials: null

Current:
DEZFULIAN, CAMERON MD
Parsed:
Business Name: null 
First and Middle Name: Cameron
Last Name: Dezfulian
Credentials: MD 

Current:
WOOTEN PILSON, KELLY L. AU
Parsed:
Business Name: null 
First and Middle Name: Kelly L.
Last Name: Wooten Pilson
Credentials: AU

Current:
ROSS BIDDLE, ALICE CNA
Parsed: 
Business Name: null 
First and Middle Name: Alice
Last Name: Ross Biddle
Credentials: CNA

Current:
CUNNINGHAM, DANIEL J. OD
Parsed: 
Business Name: null 
First and Middle Name: Daniel J.
Last Name: Cunningham
Credentials: OD

Current: 
VIENNA PODIATRY LTD
Parsed: (do not parse- business name). Copy text into Business Name column.
Business Name: Vienna Podiatry LTD
First and Middle Name: null
Last Name: null
Credentials: null 

Current:
NGUYEN, MINH CHAU T. DPM
Parsed:
Business Name: null 
First and Middle Name: Minh Chau T.
Last Name: Nguyen
Credentials: DPM

I hope it is possible to create this macro or something close to it.
I?ve studied the ?Best Way to Parse Text in Excel for Non-Uniform
Entries? that was posted in this forum but I could not figure out how
to relate that information to this situation. I?ve also tried the
?Text to Columns? wizard in Excel but that did not help either because
of the inconsistent format from one cell to the next (i.e., some names
had middle names or initials, some did not have a middle name or
initial at all causing the results to be very inconsistent).

I?ve written a macro that does exactly what I want it to do but only
works on the first row. I don?t know how to get it to work on all the
other rows at the same time. Besides, I don?t really know what I?m
doing when it comes to writing macros and I think the code I have is
very inefficient.

I?d be so grateful if someone could help me out. Thanks!

---

Request for Question Clarification by answerguru-ga on 01 Apr 2006 21:09 PST

Hello - if you have already done this macro to parse a single row
perhaps you could post the VBA code for that so that a researcher can
use it as a starting point.

As you've phrased your question, your list price appears a low. This
may change if you can provide the partial work.

answerguru-ga

---

Clarification of Question by celiasocelia-ga on 02 Apr 2006 08:59 PDT

Good idea. Here is the code. This works for the first line but that's
it. I need to get it to work on subsequent rows in the spreadsheet.
Regarding the price... I've never used this service before and I was
basing the price on a similar question that was answered. I'm willing
to pay a fair price but honestly I don't know what a fair price is for
this.

Maybe a more straightforward question would be: how can I take the
current code (shown below) and have it loop through all the rows on
the spreadsheet? Thanks!

Sub Automate_First_Row()
'
' Insert 11 columns
    Columns("A:K").Select
    Selection.Insert Shift:=xlToRight
    Range("A1").Select
'

' Put formulas into the first row of the spreadsheet
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=SEARCH("", "",RC[15])+2"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=SEARCH(RC[3],RC[14])"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(RC[-2]),"""",PROPER(MID(RC[13],RC[-2],RC[-1]-RC[-2])))"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(RC[-3]),"""",PROPER(LEFT(RC[12],RC[-3]-3)))"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=IF(ISERROR(RC[-4]),"""",RIGHT(RC[11],3))"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(RC[-5]),PROPER(RC[10]),RC[-3]&"" ""&RC[-2]&"", ""&RC[-1])"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=PROPER(RC[11])"
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "=PROPER(RC[9])"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=PROPER(RC[10])"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=RC[10]"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[10],5)&""-""&RIGHT(RC[10],4)"
 End Sub