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

[code=vb]
'Uses the Excel VBA split function
Sub MyExcelSplitCel ls()
Dim TempArray As Variant
Dim rwIndex, colIndex
With Sheet3
For rwIndex = 1 To .UsedRange.Rows .Count
TempArray = mySplit(Trim(.C ells(rwIndex, 1).Text))
For colIndex = 2 To UBound(TempArra y) + 2
.Cells(rwIndex, colIndex).Value = TempArray(colIn dex - 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(cou nter)) > 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
[/code]

the Raw Data looks like: [code=Text]
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)
[/code]

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 :

[code=vb]
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
'
[/code]

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:

[code=vb]
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

[/code]

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

[Killer42]

Just use Chuck Pearson's formulas or code, http://www.cpearson.co m/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