complex split string (delimit-text to columns)

**EXLwiz** · 05-07-2014, 02:04 PM

Hi i need a function to split names from a single column to many.

We have a column where 2 or more party names may be in 1 cell, like "John Smith and Jane Smith".

Names can also be like "John A. Smith" or "John A. Smith and Mary Smith"

the end result would be columns on 1 row that look like
A= (source data column)
B= JOHN A.
C= SMITH
D= MARY
E= SMITH

when i do this manually, i do a find/replace on " and ", change to a PIPE |,
then i used data/text to columns/set delimiter to | and split so i now have 2 cols with a full party name in each column b and c. then i add 3 or 4 columns between those 2.
then i delimit spaces on each of the combined names columns.
then i concat fname+mi columns back, then copy/paste values over the formulas and clean up the columns to reorder..

as you can see, many manual steps so would be time consuming and confusing to my very basic general excel users. these manual splits and concats are alot to ask them to do manually.

i tried recording a macro while i did a simple text to columns, but when i ran it the workbook went crazy.. plus given strict security on our network i think vba code to replicate the above would be best solution.

i'm also uploading a demo worksheet with raw data names as they would appear on my sheet, any help/suggestions would be so much appreciated, thanks!

**alansidman** · 05-07-2014, 02:11 PM

I am confused by your demo worksheet. I thought I understood what you wanted until I looked at the desired results. There are names in the desired results that don't match the raw data and combinations that I am not sure where the names are coming from. Did you upload the wrong sheet? Or maybe you can explain the rationale if it is different from what you explained in your narrative.

**EXLwiz** · 05-07-2014, 03:29 PM

SORRY my oops!

uploading a correct demo.

**tom.hogan** · 05-07-2014, 04:27 PM

Here you go, please note that in your original post said the complete names were in column A while your examples show it in column C, I did it assuming column c was right.

Sub SplitNames()

Dim vNames() As Variant
Dim s2Names(1 To 2) As String
Dim i As Long

  vNames = Range(Range("C2"), Range("C2").End(xlDown)).Value
  ReDim Preserve vNames(1 To UBound(vNames), 1 To 5)

  For i = 1 To UBound(vNames)
    If InStr(vNames(i, 1), " and ") Then
        s2Names(1) = Left(vNames(i, 1), InStr(vNames(i, 1), "and") - 2)
        s2Names(2) = Mid(vNames(i, 1), InStrRev(vNames(i, 1), "and") + 4)
        vNames(i, 2) = Left(s2Names(1), InStrRev(s2Names(1), " ") - 1)
        vNames(i, 3) = Mid(s2Names(1), InStrRev(s2Names(1), " ") + 1)
        vNames(i, 4) = Left(s2Names(2), InStrRev(s2Names(2), " ") - 1)
        vNames(i, 5) = Mid(s2Names(2), InStrRev(s2Names(2), " ") + 1)
      Else
        vNames(i, 2) = Left(vNames(i, 1), InStrRev(vNames(i, 1), " ") - 1)
        vNames(i, 3) = Mid(vNames(i, 1), InStrRev(vNames(i, 1), " ") + 1)
    End If
  Next i
 

  Range(Range("C2"), Range("G" & i)).Value = vNames()

End Sub

**EXLwiz** · 05-07-2014, 04:41 PM

thank you so much.. now a silly question, how to run this as regular users who are not going to open developer tools and debug?

can it be run automatically on a clipboard event, like on paste (users will paste the names in col C - and yes, col C was correct)

**EXLwiz** · 05-07-2014, 06:26 PM

ok nevermind...

i figured out how to add a keyboard shortcut to run it.

and it works PERFECTLY... thanks again!!

**EXLwiz** · 05-07-2014, 08:39 PM

sorry can i throw in two more parameters to consider?

what if i have names like this:

John Van DeKamp
Susan Mc Donald

yanno those pesky users, they do weird inputs sometimes and put spaces where they really dont belong.. is there a way we can check for these common ones in the string and like concat the last names that should obviously be in a single field together?

ex: VAN, VON, MC, MAC, SAN, DE, DI and maybe any others you could think of?

and i guess we should check for suffixes too...? these should also appear in the last name field:
jr, sr, II, III, IV (these are the most common i see in the batches i work with)

**tom.hogan** · 05-08-2014, 08:49 AM

While the suffixes is easy enough the Van , Von , etc. is problematic because some of them could be first names as well. I do have a solution but it requires a specific format for first and middle names. If the only possible entries are:
1) First Name
2) First name, middle initial
3) First Initial, middle name

Then the code is easy enough, but if there are other options such as first name, middle name or first initial only, I don't think it is possible.

Before I begin coding, please let me know which way you want to go
1) Restrict Last names to no spaces
2) Restrict First and middle names per above

Note there is still the problem of two name last names such as "Smith Jones" and I don't think there is any solution for this?

Thanks,

**EXLwiz** · 05-08-2014, 10:04 AM

i understand this dilemma. i'm thinking middle initials should be considered a length or 1 or 2 chars (counting a period), OR, we can maybe clean the string of any punctuation if that complicates things?

so I have a piece of VB code i was toying with a while ago for another project (it's not really complete i was strating to edit but got moved to something else before i could finish.. so it does not necessarily work in this state, but i think you'll get the gist)

This VB code creates an array from space delimiters. Then eval's each string in the array for given conditions. what i was thinking of working out in this VB code was to check for number of SPACES in the string and let that guide what happens next..

so TYPICALLY we assume it's a name like JOHN R. VAN DYKE, we count 3 spaces in the string, that splits the array into 4 strings. We can assume the 1st is always the first name. the 2nd should be eval's for length, and whatever is left can be considered the last name field.

in the case of 2 names in the field, the first check would always (as you already do in your vba) is check for " and " in the string to split each into the unique seprate parties, then apply the rules of the first part i mentioned above to each of the 2 names. So the first array would be creating 2 strings of seprate parties, then sub-arrays to determine first/last name field placement.. if that makes sense?

obviously we cant be mind readers or configure full-proof code that will 100% get every name split exactly to the correct fields, but i think we'd be at 95-98% accurate doing it this way..

I just don't know how to write this for vba.

also know, i meant to build and return an XML string from the VB code that would return nodes containing firt-last name of party 1 and f-l of party 2, which is difft than what you're doing in the vba. but here is that VB code, if it can inspire a resolution? (I added a few comments for you in there too.)

    Public Shared Function Parse_Name(ByVal namesString As String) As String

        Dim newNameStr As String = "" 
        Dim strParts() As String
        Dim intParts As Integer = 0
        Dim strP1() As String
        Dim i As Integer = 0
        Dim j As Integer = 0


        Dim BWR_NAME As String = ""

        'remove leading spaces
        namesString = LTrim(namesString)

        ' Replace double spaces - 3 is an arbitrary number
        For i = 1 To 3
            namesString = namesString.Replace("  ", " ")
        Next

        ''remove characters that you don't need (todo-KEEP HYPHENS -)
        'namesString = namesString.Replace(",", String.Empty).Replace(".", String.Empty).Replace("/", String.Empty)


        'check for 2 party names
        If namesString.Contains(" and ") Then
            namesString = namesString.Replace(" and ", "|")
        End If

'--- then i have not done any futher editing beyond the above ----- 

        'Set initial letters to uppercase
        namesString = Globalization.CultureInfo.CurrentCulture.TextInfo.ToTitleCase(namesString)

        'split the name on the sapces
        strParts = namesString.Split(" "c)
        intParts = strParts.Length

        ' This field is used later to identify which parts of strParts belong in the first name 
        ' and which in the last name. value of "F" is first name, value of "L" is last name

        ReDim strP1(intParts - 1)

        'if it's a one word name, it goes into the last name field
        If intParts = 1 Then
            strP1(0) = "L"
        Else
            strP1(0) = "F"
            If intParts = 2 Then
                '2-part names automatically go into first and last
                strP1(1) = "F"
            End If
        End If

        'check for common last name parts and suffixes and
        ' format name accordingly
        If intParts > 2 Then
            'set all parts to first name
            For i = 0 To intParts - 1
                strP1(i) = "F"
            Next


'--- the CASES, can we do a check if string "contains" in VBA? and compare for the below last name parts? --- 


            'search for last name parts and suffixes
            Select Case UCase(strParts(intParts - 1))
                Case "MD", "PHD", "JR", "SR", "ESQ", "I", "II", "III", "IV", "V"
                    strP1(intParts - 1) = "L"
                    strP1(intParts - 2) = "L"
                    Select Case UCase(strParts(intParts - 3))
                        Case "DEL", "DE", "VAN", "VON", "DER", "MC", "MAC", "SAN", "DI"
                            strP1(intParts - 3) = "L"
                    End Select
                Case Else
                    strP1(intParts - 1) = "L"
                    Select Case UCase(strParts(intParts - 2))
                        Case "DEL", "DE", "VAN", "DER"
                            strP1(intParts - 2) = "L"
                    End Select
            End Select
            For i = 0 To intParts - 2
                If UCase(strParts(i)) = "VAN" And UCase(strParts(i + 1)) = "DER" Then
                    For j = i To intParts - 1
                        strP1(j) = "L"
                    Next
                    Exit For
                End If
            Next
        End If
        'For i = 0 To intParts - 1
        '    'Select Case strP1(i)
        '    '    Case "F"
        '    '        newNameStr += strParts(i) & " "
        '    '    Case "L"
        '    '        newNameStr += strParts(i) & " "
        '    'End Select
        'Next
        Return newNameStr
    End Function

**tom.hogan** · 05-08-2014, 11:00 AM

I considered using the split function but I'm not sure it gives you much as you then have to reassemble the name. I also considered the use of number of spaces to help but while it is great for 0 and 1 space, it is of little use for more than that, e.g.

Tom Van Dyke
could be Tom/Van Dyke or Tom Van/Dyke
Tom T. Van Dyke
Could be Tom T./Van Dyke or Tom T. Van/Dyke

Regardless I will go for the 90% solution. Can you please tell me the possible entries for first and middle? I assume first can be a name or initial and middle can only be a name or initial?

Thanks
As I said the suffix thing is relatively easy, it is the middle name/initial that makes it hard. If the middle name can only be an initial with or without a period then easy - otherwise I don't have a solution.

**EXLwiz** · 05-08-2014, 11:23 AM

hmm. TBH I almost never see a first initial only in the data i work with (doesnt mean it NEVER occurs, but playing by common patterns and percentages its not very likely - but a Joe Von Helsing is much more common pattern, weher Von Helsing is a complete last name)..

so i would think first is always an actual name with no spaces, except as you are already doing for MI.. MI as you already have it is working beautifully, so i think not change that at all...,

i guess i dont understand how your existing code works right now - not for a lack of looking or trying to follow, but a lack of vba skills haha.. i was going to try and tweak your code for these things we're discussing now but didnt comprehend it enough to attempt..

someday maybe..

So anyhoo i guess use your own best jugment how to get JACK A VON SMITH to show as
first= JACK A
last= VON SMITH

is there really no way to do it to compare the literal string values like my VB code was doing (DE DI VON VAN MC etc)? If not I trust you to do what you think will result in the best accuracy.

and a million billion thanks for your help on this :D

**EXLwiz** · 05-08-2014, 12:42 PM

ok as i re-read your comments and my reply.. i know i wasnt very clear in my answer, because as i say i cant follow your code too well so i'm not sure how to answer.. but if i can try and answer this part

If the middle name can only be an initial with or without a period then easy

yes that is true - treat it as that MI can only be a single letter, then (maybe a period) then a space, and everything after the space following the MI is the last name.

so first can only be a name, middle can only be an initial, then suffixes as we discussed, and everything between MI and suffix is last name.

does that clear up what you were trying to confirm?

**tom.hogan** · 05-08-2014, 02:14 PM

OK, I decided to use the split function and not focus on the middle name. Based on limited testing, I believe this works for every case except for when there is an "uncommon" two name last name - like Smith Jones. You can account for this by having your operators enter these with a hyphen i.e. Smith-Jones - then I believe it will work for all cases.
To prevent redundant code I had to add a function for parsing the names - copy all of the below into a single module.

Private Sub FillNames()

Dim vNamesArr() As Variant
Dim sNamesArr() As String
Dim s2NamesArr(1 To 2) As String
Dim sTempArr() As String
Dim lStartIndex As Long
Dim i As Long

  
  vNamesArr = Application.WorksheetFunction.Transpose(Range(Range("C2"), Range("C2").End(xlDown)).Value)
 
  lStartIndex = Range("D2").End(xlDown).End(xlDown).End(xlUp).Row
  ReDim sNamesArr(lStartIndex To UBound(vNamesArr), 1 To 4)

  For i = lStartIndex To UBound(vNamesArr)
  vNamesArr(i) = UCase(Trim(vNamesArr(i)))
  Do While InStr(vNamesArr(i), "  ")
    vNamesArr(i) = Replace(vNamesArr(i), "  ", " ")
  Loop
  If InStr(vNamesArr(i), " AND ") Then
      s2NamesArr(1) = Left(vNamesArr(i), InStr(vNamesArr(i), "AND") - 2)
      s2NamesArr(2) = Mid(vNamesArr(i), InStrRev(vNamesArr(i), "AND") + 4)
      sTempArr = ParseName(s2NamesArr(1))
      sNamesArr(i, 1) = sTempArr(1)
      sNamesArr(i, 2) = sTempArr(2)
      sTempArr = ParseName(s2NamesArr(2))
      sNamesArr(i, 3) = sTempArr(1)
      sNamesArr(i, 4) = sTempArr(2)
    Else
      sTempArr = ParseName(CStr(vNamesArr(i)))
      sNamesArr(i, 1) = sTempArr(1)
      sNamesArr(i, 2) = sTempArr(2)
  End If
  Next i
 
  Range(Range("D" & lStartIndex + 1), Range("G" & i)).Value = sNamesArr()

End Sub

Private Function ParseName(Name As String) As String()

Dim vNameArr As Variant
Dim sNameArr(1 To 2) As String
Dim sNameTypeArr() As String
Dim lItem As Long
Dim i As Long

  vNameArr = Split(Name, " ")
  lItem = UBound(vNameArr) + 1
  ReDim Preserve vNameArr(1 To lItem)
 
 If lItem = 1 Then
    sNameArr(1) = ""
    sNameArr(2) = vNameArr(1)
    ParseName = sNameArr
    Exit Function
  End If
  
  ReDim sNameTypeArr(1 To lItem)

  vNameArr(lItem) = Replace(vNameArr(lItem), ".", "")
  Select Case vNameArr(lItem)
    Case "MD", "PHD", "JR", "SR", "ESQ", "I", "II", "III", "IV", "V"
      sNameTypeArr(lItem) = "L"
      lItem = lItem - 1
  End Select

  sNameTypeArr(lItem) = "L"
  lItem = lItem - 1

  Select Case vNameArr(lItem)
    Case "DE", "DEL", "DI", "VAN", "VON", "DER", "MC", "MAC", "SAN"
      sNameTypeArr(lItem) = "L"
      lItem = lItem - 1
  End Select
  
  For i = 1 To lItem
    sNameTypeArr(i) = "F"
  Next i
  
  For i = 1 To UBound(sNameTypeArr)
    If sNameTypeArr(i) = "F" Then
        sNameArr(1) = sNameArr(1) & vNameArr(i) & " "
      Else
        sNameArr(2) = sNameArr(2) & vNameArr(i) & " "
    End If
  Next i

  sNameArr(1) = Trim(sNameArr(1))
  sNameArr(2) = Trim(sNameArr(2))
  ParseName = sNameArr
 
End Function

Regards,

Tom

**EXLwiz** · 05-08-2014, 10:20 PM

first of all THANK YOU for this amazing effort. you did in a very short time what probably would have taken me weeks or longer to resolve on my own...

so i've been doing my best to break it when testing

(better us than the users right??) well sadly it ook a while but i did finally break it, but now i'm just not sure why.

at first i thought its because in a list of 1700+ names, there were 2 rows many hundred rows apart from each other that were blank. that was an unexpected scenario even for me! so it throws an error 9 subscript out of bounds.

so i debugged a little bit...

line 15 in FillNames

ReDim sNamesArr(lStartIndex To UBound(vNamesArr), 1 To 4)

and line 52 in Parser

  ReDim Preserve vNameArr(1 To lItem)

those 2 are where the eror hit on a few difft tries.

My initial belief was in vba Line 50, the first actual row of code after variables are declared in ParseName function, assumes that Name is not an empty string (somehow the empty got thru to this function?) and attempts to split the array.

ok if it's because we hit an empty row, fine i get it, i sorted to clear the empty rows out and tried again, got to row 205 and threw the same error, this time while debugging and able to see the full contents of the main array that would hole the 1710 rows array sets. i cant even guess why this error occurs, since there's nothing unusual about that particular nameset (like jane k. doe and husband doe), and the 200 rows above it follow the same pattern more than once, and the next name down also looks fine..i also see you trim leading and trailing spaces from the string before parse, and there are no double spaces or bad chars, i checked (and you also address dbl spaces in code)

one other thing i tried.. i manually split the 2 party names into 2 columns with my text to cols " and " = | then tried running just the single party name cols thru and it worked for each column.

so i dunno why its throwing this error, can you elaborate or figure out what's happening?

heres the actual list i tested with macroDemo-SplitNames.xlsm

**EXLwiz** · 05-09-2014, 09:20 AM

@tom.hogan: EPIPHANY!!

i know why it is breaking on this same name over and over.

the name its breaking on is cANDace. we are splitting on "and" instead of " and " in the new code you posted yesterday. it did not have a leading and trailing space in the new code you did yesterday.. that 'splained it Lucie!! i added the spaces back and now it's all better!

[edit]- i also had to change the -2 to -1, since it was trimming the last letter of the 1st party last name where theres " and " in bettween names... new code looks like this

Sub FillNames()

Dim vNamesArr() As Variant
Dim sNamesArr() As String
Dim s2NamesArr(1 To 2) As String
Dim sTempArr() As String
Dim lStartIndex As Long
Dim i As Long

  
  vNamesArr = Application.WorksheetFunction.Transpose(Range(Range("C2"), Range("C2").End(xlDown)).Value)
 
  lStartIndex = Range("D2").End(xlDown).End(xlDown).End(xlUp).Row
  
  ReDim sNamesArr(lStartIndex To UBound(vNamesArr), 1 To 4)

  For i = lStartIndex To UBound(vNamesArr)
  vNamesArr(i) = UCase(Trim(vNamesArr(i)))
  Do While InStr(vNamesArr(i), "  ")
    vNamesArr(i) = Replace(vNamesArr(i), "  ", " ")
  Loop
  
  If InStr(vNamesArr(i), " AND ") Then
      s2NamesArr(1) = Left(vNamesArr(i), InStr(vNamesArr(i), " AND ") - 1)
      s2NamesArr(2) = Mid(vNamesArr(i), InStrRev(vNamesArr(i), " AND ") + 4)
      sTempArr = ParseName(s2NamesArr(1))
      sNamesArr(i, 1) = sTempArr(1)
      sNamesArr(i, 2) = sTempArr(2)
      sTempArr = ParseName(s2NamesArr(2))
      sNamesArr(i, 3) = sTempArr(1)
      sNamesArr(i, 4) = sTempArr(2)
    Else
      sTempArr = ParseName(CStr(vNamesArr(i)))
      sNamesArr(i, 1) = sTempArr(1)
      sNamesArr(i, 2) = sTempArr(2)
  End If
  Next i
 
  Range(Range("D" & lStartIndex + 1), Range("G" & i)).Value = sNamesArr()

End Sub

so that resolves that mystery, but is there something which can be done about it breaking on empty rows in between rows, like

1 - name
2 - name
3 - (blank-empty string)
4 - name

right now if i ran this code it doesnt split any rows beyond row2.

**tom.hogan** · 05-09-2014, 09:36 AM

Originally Posted by EXLwiz

but is there something which can be done about it breaking on empty rows in between rows, like

1 - name
2 - name
3 - (blank-empty string)
4 - name

right now if i ran this code it doesnt split any rows beyond row2.

I think we were posting at the same time, per my previous post I have corrected for this. Please try my new code, it should be good-to-go!

Regards,

Tom

**EXLwiz** · 05-09-2014, 09:59 AM

I think we were posting at the same time, per my previous post I have corrected for this. Please try my new code, it should be good-to-go!

Regards,

Tom

LOL yes it appears we were.. now worries, we all make simple mistakes like that.. as i said, its why we test and debug so the users never see our bumbles (if we can help it!!)

ok i think between that AND fix and the new code you just put up that i will be playing with today, i shouold be able to make my own tweaks for some of those other unexpected conditions.. cuz your right, we cant get 100%, we did say 90%.. and the longer i debug the better i am able to follow the code so i may be alright on my own from here anyway.

so i think i can let you off the hook now...

again many many HUUUGE thanks for the assist on this - you're a rock star!!!

**tom.hogan** · 05-09-2014, 09:26 AM

Sorry, dumb mistake. When I was separating two person names I was using "AND" instead of " AND " which would improperly split up names with AND in them such as C"and"ace on Row 205. I have corrected for that. I also made a few other changes:
1) Added code to deal with an empty line in Column C.
2) If the macro is run with no new names it will cause an error - added a message box for this issue.
3) Corrected for when First Name is a common name i.e. VAN SMITH would have returned "VAN SMITH" in the last name - now fixed to return "VAN" and "SMITH"

Here is the corrected code.

Sub FillNames()

Dim vNamesArr() As Variant
Dim sNamesArr() As String
Dim s2NamesArr(1 To 2) As String
Dim sTempArr() As String
Dim lStartIndex As Long
Dim i As Long

  
  vNamesArr = Application.WorksheetFunction.Transpose(Range(Range("C2"), Range("C65536").End(xlUp)).Value)
 
  lStartIndex = Range("D2").End(xlDown).End(xlDown).End(xlUp).Row
  
  If lStartIndex > UBound(vNamesArr) Then
    MsgBox "There were no new names entered! Only run this macro when there is at least " & _
           "one new name" & vbCrLf & vbCrLf & "Select OK to exit macro.", _
           vbCritical, "FillList Macro Error"
           Exit Sub
  End If
  
  ReDim sNamesArr(lStartIndex To UBound(vNamesArr), 1 To 4)

  For i = lStartIndex To UBound(vNamesArr)
  vNamesArr(i) = UCase(Trim(vNamesArr(i)))
  Do While InStr(vNamesArr(i), "  ")
    vNamesArr(i) = Replace(vNamesArr(i), "  ", " ")
  Loop
  If InStr(vNamesArr(i), " AND ") Then
      s2NamesArr(1) = Left(vNamesArr(i), InStr(vNamesArr(i), " AND ") - 1)
      s2NamesArr(2) = Mid(vNamesArr(i), InStrRev(vNamesArr(i), " AND ") + 5)
      sTempArr = ParseName(s2NamesArr(1))
      sNamesArr(i, 1) = sTempArr(1)
      sNamesArr(i, 2) = sTempArr(2)
      sTempArr = ParseName(s2NamesArr(2))
      sNamesArr(i, 3) = sTempArr(1)
      sNamesArr(i, 4) = sTempArr(2)
    Else
      sTempArr = ParseName(CStr(vNamesArr(i)))
      sNamesArr(i, 1) = sTempArr(1)
      sNamesArr(i, 2) = sTempArr(2)
  End If
  Next i
 
  Range(Range("D" & lStartIndex + 1), Range("G" & i)).Value = sNamesArr()

End Sub
Private Function ParseName(Name As String) As String()

Dim vNameArr As Variant
Dim sNameArr(1 To 2) As String
Dim sNameTypeArr() As String
Dim lItem As Long
Dim i As Long

  If IsEmpty(Name) Or Name = "" Then
    sNameArr(1) = "empty"
    sNameArr(2) = ""
    ParseName = sNameArr
    Exit Function
  End If
  
  vNameArr = Split(Name, " ")
  lItem = UBound(vNameArr) + 1
  ReDim Preserve vNameArr(1 To lItem)
 
 If lItem = 1 Then
    sNameArr(1) = vNameArr(1)
    sNameArr(2) = ""
    ParseName = sNameArr
    Exit Function
  End If
  
  ReDim sNameTypeArr(1 To lItem)

  vNameArr(lItem) = Replace(vNameArr(lItem), ".", "")
  Select Case vNameArr(lItem)
    Case "MD", "PHD", "JR", "SR", "ESQ", "I", "II", "III", "IV", "V"
      sNameTypeArr(lItem) = "L"
      lItem = lItem - 1
  End Select

  sNameTypeArr(lItem) = "L"
  lItem = lItem - 1
  If lItem > 1 Then
      Select Case vNameArr(lItem)
      Case "DE", "DEL", "DI", "VAN", "VON", "DER", "MC", "MAC", "SAN"
        sNameTypeArr(lItem) = "L"
        lItem = lItem - 1
    End Select
  End If
  
  For i = 1 To lItem
    sNameTypeArr(i) = "F"
  Next i
  
  For i = 1 To UBound(sNameTypeArr)
    If sNameTypeArr(i) = "F" Then
        sNameArr(1) = sNameArr(1) & vNameArr(i) & " "
      Else
        sNameArr(2) = sNameArr(2) & vNameArr(i) & " "
    End If
  Next i

  sNameArr(1) = Trim(sNameArr(1))
  sNameArr(2) = Trim(sNameArr(2))
  ParseName = sNameArr
 
End Function

Please note, I suspect there are other conditions that would cause an error. I just don't have the time to code all the possible error checking and per previous post this should be a 90% solution.

Regards,

complex split string (delimit-text to columns)

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

complex split string (delimit-text to columns)

Re: complex split string (delimit-text to columns)

Re: complex split string (delimit-text to columns)

Re: complex split string (delimit-text to columns)

Re: complex split string (delimit-text to columns)

Re: complex split string (delimit-text to columns)

Re: complex split string (delimit-text to columns)

Re: complex split string (delimit-text to columns)

Re: complex split string (delimit-text to columns)

Re: complex split string (delimit-text to columns)

Re: complex split string (delimit-text to columns)

Re: complex split string (delimit-text to columns)

Re: complex split string (delimit-text to columns)

Re: complex split string (delimit-text to columns)

Re: complex split string (delimit-text to columns)

Re: complex split string (delimit-text to columns)

Re: complex split string (delimit-text to columns)

Re: complex split string (delimit-text to columns)

Thread Information

Users Browsing this Thread

Similar Threads

How to split text from text string into separate columns...no delimiters!

[SOLVED] Split text string into 5 columns

Advanced Split Function: Need to pull text string in quotes, within a text string

Delimit string to columns after using TRIM?

Split text string to different columns

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions