Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Parsing problem (XP and 2000)

    I'm trying to parse the data in a single field that contains the title and full name. I'm using the sample found in the Microsoft Knowledge Base ( http://support.microsoft.com/default.aspx?...=kb;en-us;72573 ). That sample shows how to take a string such as "Mary J. Smith" and break it out into three separate fields.

    My problem is that I have cells that contain, e.g., "Dr. and Mrs. John B. Smith". I want to break up that string into four fields: Title (Dr. and Mrs.); FName (John); MI (B.); LName (Smith). Sorry, but I still don't know enough about such things to modify the formula. Can anyone help me, please. Thank you!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parsing problem (XP and 2000)

    The answer depends on how consistent the names are.
    1. <LI>Is there always a first name? Or are there names like "Dr. R.K. Narayan"?
      <LI>Does every name contain a middle initial? Or are there names like "Mr. James Levitt"?
      <LI>Is there always a title? Or are there names like "John B. Smith"?

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Parsing problem (XP and 2000)

    Hello, Hans

    [list=1]<LI>In this table, yes

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parsing problem (XP and 2000)

    Lucas, there is no special "parsing" language; the example from Microsoft just uses the built-in worksheet functions for text manipulation, such as SEARCH, LEFT, MID and RIGHT. You can look them up in the online help.

    In your case, it is more complicated, since the name can consist of a variable number of words. One way would be to use a fixed list of possible "titles". I have chosen not to do this; this means that we have to parse the name backwards, starting at the end. There is no built-in worksheet function to search the cell in a text backwards, but there is one in VBA: InstrRev (introduced in Office 2000 VBA). I created a little wrapper to be able to use it in a cell:

    Function SearchReverse(strName As String, strChar As String, Optional intStart As Integer = -1)
    SearchReverse = InStrRev(strName, strChar, intStart)
    End Function

    I use a number of intermediary results to get the result:
    1. <LI>LastSpace: position of the last space in the name.
      <LI>LastName: everything to the right of LastSpace.
      <LI>LastPeriodBeforeSpace: the position of the last period before the last space.
      <LI>HasMiddleInitial: if LastPeriodBeforeSpace is immediately before the last space, we have a middle initial.
      <LI>NextToLastspace: position of the next to last space in the name.
      <LI>MiddleInitial: if we have one, it is everything between the next to last space and the last space.
      <LI>SecondToLastSpace: position of the second to last space in the name (0 if not available)
      <LI>FirstName: if we have a middle initial, everything between the second to last space and next to last space; otherwise, everything between the next to last space and the last space.
      <LI>Title: everything from the beginning to the second to last space or next to last space, depending on whether we have a middle initial.
    Confused now?

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Parsing problem (XP and 2000)

    Totally! <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    Truth is, your example is fascinating! I'm sure I could not explain the workings of that spreadsheet to anyone else. I just need to put my face into the Excel Help file and study functions. Your example will be a huge help!

    I noticed the sample Excel spreadsheet has a blank column (F). Does that blank column have a purpose or...?

    Thank you very much!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parsing problem (XP and 2000)

    Hi Lucas,

    I left column F blank to separate the end results (title, first name, middle initial and last name) from the columns used solely as intermediate results. You can remove column F, and/or hide the columns with the intermediate results as you please.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Parsing problem (XP and 2000)

    I understand. Thanks again, Hans!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •