Break down string by delimiter and concatanate with another broken down cell

I need help Completing a VBA UDF that will take a string in a certain format, and return it in a different one.
I have to "assisting" UDFs for that.. This is what I have so far:
Function STR_SPLIT(str, sep, n) As String 'Splits String by delimiter and return array in n position
    Dim V() As String
    V = Split(str, sep)
    STR_SPLIT = V(n - 1)
End Function

Function COUNTTEXT(ref_value As Range, ref_string As String) As Integer 'Counts how many times a certain character appears in a given cell

Dim i As Integer, count As Integer

count = 0
If Len(ref_string) <> 1 Then COUNTTEXT = CVErr(xlErrValue): Exit Function
For i = 1 To Len(ref_value.Value)
    If Mid(ref_value, i, 1) = ref_string Then count = count + 1
Next

COUNTTEXT = count

End Function

Function LDAPFORM(SourceOU As Range, SourceFQDN As Range) As String
    'First we need to check if the original Cell is empty.
    If IsEmpty(SourceOU) Then
        LDAPFORM = ""
        Exit Function
    End If
    'If the SourceOU is not empty, we will need to count how many times the "\" character appears to make the right Result String
    Dim cnt As Integer
    cnt = COUNTTEXT(SourceOU, "\")
    Dim i As Integer
    For i = 0 To cnt
    Next
End Function

Open in new window


Basically I have Two cells,
One is called Source OU That has several Strings seperated by "\" For exmple : IL\Administration\Junior
and another Called SourchFQDN that should have some sort of FQDN for example: mydom.local

What I need my UDF to do is to take these two cells and return it in a valid LDAP format, in this case:
"OU=Junior,OU=Administration,OU=IL,DC=mydom,DC=local"
The OU should be returned in reverse order.
Please see attached File for example (File is Macro-enabled!)
userlist.xlsx.xlsm
LVL 8
David SankovskySenior SysAdminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hi David,

You forgot to upload the file.
0
David SankovskySenior SysAdminAuthor Commented:
So I did, My apologies.
File uploaded now
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this...

Function OUCN(ByVal OU As String, ByVal FQDN As String) As String
Dim arrOU() As String, arrFQDN() As String
Dim i As Long
Dim str As String
arrOU = Split(OU, "\")
For i = UBound(arrOU) To 0 Step -1
    If LCase(arrOU(i)) Like "[a-z]*" Then
        If str = "" Then
            str = "OU=" & arrOU(i)
        Else
            str = str & "," & "OU=" & arrOU(i)
        End If
    End If
Next i
arrFQDN = Split(FQDN, ".")
For i = 0 To UBound(arrFQDN)
    str = str & "," & "DC=" & arrFQDN(i)
Next i
OUCN = str
End Function

Open in new window

In the attached, the function is placed on Module2 and the formula is placed in column D.

Is that what you are trying to achieve?
userlist.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David SankovskySenior SysAdminAuthor Commented:
Simple and efficient. Thank you very much!
1
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome David! Glad it worked as desired.
Thanks for the feedback.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.