Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2002

    Unanswered: Find a character

    I have a data like this :

    Michael A. White, Mr
    John Garcia W. Omeso Smith, Mr

    What I want is to extract the first, middle and last name.
    First name is the easiest since I just need to find the
    first space and use left function.

    However, last name is a bit tricky since there could be more than 1 space after the first space. So, what I need to find is the location of the last space before the coma, so can I use mid function to get the last

    For the middle name, I can also use mid function I

    Any suggestion ?


  2. #2
    Join Date
    Feb 2003
    Auckland, NZ

    Re: Find a character

    Hope this code helps you.
    I added the comments

    Sub test()
    Dim SrcStr As String

    'Define two Arrays
    Dim CurStr() As String
    Dim StrVals() As String

    ' Define two variable to be used fo Array indexes
    Dim CurInt As Long
    Dim StrInt As Long

    ' Define two variable to get maximum indexes of arrays
    Dim MaxCur As Long
    Dim MaxStr As Long

    'Set the index to zero
    CurInt = 0

    'Get the data. I used your string in the POST
    SrcStr = "Michael A. White, Mr John Garcia W. Omeso Smith,"

    'Split the string using the commas and put it in an array
    CurStr = Split(SrcStr, ",")
    MaxCur = UBound(CurStr, 1)

    'loop through the indexes of the first array
    While CurInt <= MaxCur

    'Split the current index of the first array and add it to a
    'second array
    StrVals = Split(CurStr(CurInt), " ")
    MaxStr = UBound(StrVals, 1)

    'Do with the array what you need to do
    'I just use the debug statement to explain
    'You will either use index 0 or 1 for the first name
    'depending on if there is a title(Mr, Mrs) etc.
    'The last name will be the maximum index

    StrInt = 0

    'Loop through the indexes of the second array
    While StrInt <= MaxStr
    'Exclude empty strings
    If Not Trim(StrVals(StrInt)) = "" Then
    Debug.Print StrVals(StrInt)
    End If
    StrInt = StrInt + 1

    CurInt = CurInt + 1


    End Sub

  3. #3
    Join Date
    Oct 2002
    Leicester - UK
    jesus wept, i think you need to think a little simpler

    dim temp as string, surname as string

    temp = Left$(<name>, InStrRev(<Name>, ",") - 1) ' this losses the mr mrs what ever
    surname = Right$(temp, Len(temp) - InStrRev(temp, " "))

    and you said you could do the first and the middle names
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    Join Date
    Feb 2003
    Auckland, NZ


    I misunderstood.

    Disregard my previous post. I thought you had one string of names seperated by commas.

    m.timoney has the correct solution.


Posting Permissions

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