By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,650 Members | 1,778 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,650 IT Pros & Developers. It's quick & easy.

Splitting Text

P: 48
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
  1. 'Uses the Excel VBA split function
  2. Sub MyExcelSplitCells()
  3. Dim TempArray As Variant
  4. Dim rwIndex, colIndex
  5. With Sheet3
  6.     For rwIndex = 1 To .UsedRange.Rows.Count
  7.     TempArray = mySplit(Trim(.Cells(rwIndex, 1).Text))
  8.     For colIndex = 2 To UBound(TempArray) + 2
  9.     .Cells(rwIndex, colIndex).Value = TempArray(colIndex - 2)
  10. Next
  11. Next
  12. End With
  13. End Sub
  14. Function mySplit(str As String) As Variant
  15.     ReDim myArray(0)
  16.     Dim counter As Integer
  17.         counter = 0
  18.             For x = 1 To Len(str)
  19.             Select Case Mid(str, x, 1)
  20.             Case ",", " " '".", "-"
  21.         If Len(myArray(counter)) > 0 Then
  22.             counter = counter + 1
  23.     ReDim Preserve myArray(counter)
  24. End If
  25.  
  26. Case Else
  27.     myArray(counter) = myArray(counter) + Mid(str, x, 1)
  28. End Select
  29.     Next
  30.     mySplit = myArray
  31. End Function
  32.  
the Raw Data looks like:
Expand|Select|Wrap|Line Numbers
  1. AShok Kumar, Mr. Pandi
  2. Aanei, Mr. Paul
  3. Aarthi, Mrs. Aravamudhan
  4. Abbott, Mr. Neil
  5. Abcouwer, Mr. Eric
  6. Abd Manaf, Mrs. Fazilah
  7. Abdul Aziz, Mr. Ahmed
  8. Abdulgapul, Mr. Al-Ameen B
  9. Abdullaeva, Miss Roziya
  10. Abhishek, Mr. Banchhor
  11. Abhishek, Mr. Roy
  12. Abisamra, Mr. Atef
  13. Able, Mr. Klaus
  14. Abraham, Mr. Lionel
  15. Achuth Rao, Mr. Subramania Rao
  16. Acosta, Mr. Ernesto F.
  17. Adefioye, Mr. William
  18. Adeline Jenitha, Ms. Wilfred
  19. Adeyemi, Mr. Peter Adekunle (Peter)
  20. Adkinson, Mr. Desmond John
  21. Ageev, Mr. Fedor Borisovitch (Fedor)
  22.  
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,
Sep 4 '07 #1
Share this Question
Share on Google+
11 Replies


QVeen72
Expert 100+
P: 1,445
Hi,

Check This :

Expand|Select|Wrap|Line Numbers
  1.     Dim sSQL As String
  2.     sSQL = "AShok Kumar, Mr. Pandi"
  3.  
  4.     Dim tarr
  5.     Dim i As Integer
  6.     Dim FName As String
  7.     Dim LName As String
  8.     Dim Title As String
  9.     '
  10.     tarr = Split(sSQL, ",")
  11.     FName = tarr(0)
  12.     '
  13.     sSQL = Replace(sSQL, FName, "")
  14.     sSQL = Replace(sSQL, ",", "")
  15.     sSQL = Trim(sSQL)
  16.     '
  17.     tarr = Split(sSQL, " ")
  18.     '
  19.     Title = tarr(0)
  20.     LName = ""
  21.     For i = 1 To UBound(tarr)
  22.         LName = LName & " " & Trim(tarr(i))
  23.     Next
  24.     MsgBox Title & " " & FName & LName
  25.     '
  26.  
Regards
Veena
Sep 4 '07 #2

Expert 5K+
P: 8,434
Question: Do you trust the data to always include a title? This will make a difference to the logic.
Sep 4 '07 #3

P: 48
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,
Sep 4 '07 #4

QVeen72
Expert 100+
P: 1,445
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
Sep 4 '07 #5

P: 48
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
Sep 4 '07 #6

QVeen72
Expert 100+
P: 1,445
Hi,

Just replace your MySplit function:

Expand|Select|Wrap|Line Numbers
  1. Function mySplit(sSQL As String) As Variant
  2.     Dim tarr
  3.     Dim i As Integer
  4.     Dim FName As String
  5.     Dim LName As String
  6.     Dim Title As String
  7.     Dim NewArr(0 to 2) As String
  8.     '
  9.     tarr = Split(sSQL, ",")
  10.     FName = tarr(0)
  11.     '
  12.     sSQL = Replace(sSQL, FName, "")
  13.     sSQL = Replace(sSQL, ",", "")
  14.     sSQL = Trim(sSQL)
  15.     '
  16.     tarr = Split(sSQL, " ")
  17.     '
  18.     Title = tarr(0)
  19.     LName = ""
  20.     For i = 1 To UBound(tarr)
  21.         LName = LName & " " & Trim(tarr(i))
  22.     Next
  23.     NewArr(0) = Title
  24.     NewArr(1) = FName
  25.     NewArr(2) = LName
  26.     mySplit = NewArr
  27. End Function
  28.  
  29.  
You have used "str" as a variable name. It is a Reserverd Word and should be avoided using it as a variable.

Regards
Veena
Sep 4 '07 #7

Expert 5K+
P: 8,434
No, there are also some, who have a Doctor as Title.
My question was whether it ever has no title.
Sep 5 '07 #8

Expert 5K+
P: 8,434
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.
Sep 5 '07 #9

SammyB
Expert 100+
P: 807
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
Sep 5 '07 #10

Expert 5K+
P: 8,434
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. :)
Sep 5 '07 #11

SammyB
Expert 100+
P: 807
Geks dont no how to spel formlas
Sep 5 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.