Splitting Text

[alive84]

Hi There,

I have written a little script that should splitt FirstName, Mr.Ms.Miss and LastName. However, the raw data I received with which I will have to use my VB script, is a bit more complicated, and I can't solve that challenge yet. I need some tips from experts. :-)

'Uses the Excel VBA split function
Sub MyExcelSplitCells()
Dim TempArray As Variant
Dim rwIndex, colIndex
With Sheet3
    For rwIndex = 1 To .UsedRange.Rows.Count
    TempArray = mySplit(Trim(.Cells(rwIndex, 1).Text))
    For colIndex = 2 To UBound(TempArray) + 2
    .Cells(rwIndex, colIndex).Value = TempArray(colIndex - 2)
Next
Next
End With
End Sub
Function mySplit(str As String) As Variant
    ReDim myArray(0)
    Dim counter As Integer
        counter = 0
            For x = 1 To Len(str)
            Select Case Mid(str, x, 1)
            Case ",", " " '".", "-"
        If Len(myArray(counter)) > 0 Then
            counter = counter + 1
    ReDim Preserve myArray(counter)
End If
 
Case Else
    myArray(counter) = myArray(counter) + Mid(str, x, 1)
End Select
    Next
    mySplit = myArray
End Function
 

the Raw Data looks like:

AShok Kumar, Mr. Pandi
Aanei, Mr. Paul
Aarthi, Mrs. Aravamudhan
Abbott, Mr. Neil
Abcouwer, Mr. Eric
Abd Manaf, Mrs. Fazilah
Abdul Aziz, Mr. Ahmed
Abdulgapul, Mr. Al-Ameen B
Abdullaeva, Miss Roziya
Abhishek, Mr. Banchhor
Abhishek, Mr. Roy
Abisamra, Mr. Atef
Able, Mr. Klaus
Abraham, Mr. Lionel
Achuth Rao, Mr. Subramania Rao
Acosta, Mr. Ernesto F.
Adefioye, Mr. William
Adeline Jenitha, Ms. Wilfred
Adeyemi, Mr. Peter Adekunle (Peter)
Adkinson, Mr. Desmond John
Ageev, Mr. Fedor Borisovitch (Fedor)
 

Basically, I need the FirstName (with Middle Name) in one column, Title in another, and LastName also.

The Script I wrote works, as long as there aren't MiddleNames. Does anybody have a hint? I would highly appreciate it.

Regards,

[QVeen72]

Hi,

Check This :

    Dim sSQL As String
    sSQL = "AShok Kumar, Mr. Pandi"
 
    Dim tarr
    Dim i As Integer
    Dim FName As String
    Dim LName As String
    Dim Title As String
    '
    tarr = Split(sSQL, ",")
    FName = tarr(0)
    '
    sSQL = Replace(sSQL, FName, "")
    sSQL = Replace(sSQL, ",", "")
    sSQL = Trim(sSQL)
    '
    tarr = Split(sSQL, " ")
    '
    Title = tarr(0)
    LName = ""
    For i = 1 To UBound(tarr)
        LName = LName & " " & Trim(tarr(i))
    Next
    MsgBox Title & " " & FName & LName
    '
 

Regards
Veena

[Killer42]

Question: Do you trust the data to always include a title? This will make a difference to the logic.

[alive84]

Question: Do you trust the data to always include a title? This will make a difference to the logic.

Thanks for the ideas so far.

To Killer42:

No, there are also some, who have a Doctor as Title.

To Veena:

Thanks, I will try to use your input, but I am not really getting it why you are using SQL?

Thanks for the inputs.

Regards,

[QVeen72]

Hi,

It is sSQL >> String Variable. It was already declared, so did not change the variable name.
You have to replace it with your Text / String data.

Regards
Veena

[alive84]

hI,

IT IS sSQL >> String Variable... It was already declared , So did not change the variable name..
U have to replace it with ur Text / String data

REgards
Veena

sorry, but how do I implement that in my script. I am lost, I am not really a VB professional... :-)

thanks

[QVeen72]

Hi,

Just replace your MySplit function:

Function mySplit(sSQL As String) As Variant
    Dim tarr
    Dim i As Integer
    Dim FName As String
    Dim LName As String
    Dim Title As String
    Dim NewArr(0 to 2) As String
    '
    tarr = Split(sSQL, ",")
    FName = tarr(0)
    '
    sSQL = Replace(sSQL, FName, "")
    sSQL = Replace(sSQL, ",", "")
    sSQL = Trim(sSQL)
    '
    tarr = Split(sSQL, " ")
    '
    Title = tarr(0)
    LName = ""
    For i = 1 To UBound(tarr)
        LName = LName & " " & Trim(tarr(i))
    Next
    NewArr(0) = Title
    NewArr(1) = FName
    NewArr(2) = LName
    mySplit = NewArr
End Function
 
 

You have used "str" as a variable name. It is a Reserverd Word and should be avoided using it as a variable.

Regards
Veena

[Killer42]

No, there are also some, who have a Doctor as Title.

My question was whether it ever has no title.

[Killer42]

People, just a quick note as Moderator - please don't use TXT-style abbreviations such as "u" for "you" and "ur" for "your". The posting guidelines require "clear, concise English". I know English varies a lot, and that's to be expected. But don't deliberately mangle it.

[SammyB]

Hi There,
<snip>
Basically, I need the FirstName (with Middle Name) in one column, Title in another, and LastName also.

The Script I wrote works, as long as there aren't MiddleNames. Does anybody have a hint? I would highly appreciate it.

Regards,

Just use Chuck Pearson's formulas or code, http://www.cpearson.com/excel/FirstLast.htm
As a Microsoft MVP for Excel, his site has lots of genius! --Sam