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. :-)

Expand|Select|Wrap|Line Numbers

 
'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:

Expand|Select|Wrap|Line Numbers

 
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 :

Expand|Select|Wrap|Line Numbers

 
    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

Quote:

Originally Posted by Killer42

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

Quote:

Originally Posted by QVeen72

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:

Expand|Select|Wrap|Line Numbers

 
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

Quote:

Originally Posted by alive84

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

Quote:

Originally Posted by alive84

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

Killer42

Quote:

Originally Posted by SammyB

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

Just had a quick peek at Chuck's site. Pretty impressive!

Mind you, given the blurb on his "before you e-mail me" page about writing clearly and ensuring you have everything right and do a spell-check, I find it very disappointing that he includes so many errors. Including things that any spell-checker would pick up, like "the the" and "formlas". I'll probably e-mail him about it. Let's hope I get it exactly right, or he won't read it. :)

SammyB

Geks dont no how to spel formlas

Splitting Text

re: Splitting Text

re: Splitting Text

re: Splitting Text

re: Splitting Text

re: Splitting Text

re: Splitting Text

re: Splitting Text

re: Splitting Text

re: Splitting Text

re: Splitting Text

re: Splitting Text

Similar Visual Basic 4 / 5 / 6 bytes

/bytes/development

/bytes/it

/bytes/about