extract characters from an Excel cell 2

[fuunstatic]

a cell will contain the following data

CN=johnsmith,OU=sdjfsdj,CN=peterjones,OU=shfhjsd,CN=johndoe,OU=sdjfhdjkfhsdj,CN=janedoe,OU=shfjsdfhjsd

I would like to extract the items in between CN= and OU= (ie the names) to another cell

any one have any hints etc
(FIND functions gives me the numbered positions of "CN=" and that's as far as I got)

thanks in advance to any help

"Work to live, don't live to work"

"The problem with troubleshooting is that sometimes it shoots back"

 

[ManxDom]

Try this macro...
assuming you list is in cell A1
output will appear in cell A2

Sub Macro1()

Dim strlength As Integer
Dim loopctr As Integer
Dim filteredlist As String
Dim currstr As String

strlength = Len(Cells(1, 1).Value)
currstr = ""
For loopctr = 1 To strlength
currstr = currstr + Mid(Cells(1, 1).Value, loopctr, 1)
If Right(currstr, 3) = "CN=" Or Right(currstr, 3) = "OU=" Then
currstr = ""
loopctr = loopctr + 1
While Mid(Cells(1, 1).Value, loopctr, 1) <> "," And loopctr <= strlength
currstr = currstr + Mid(Cells(1, 1).Value, loopctr, 1)
loopctr = loopctr + 1
Wend
filteredlist = filteredlist + currstr + ","
currstr = ""
End If
Next loopctr
Cells(2, 1).Value = Left(filteredlist, Len(filteredlist) - 1)

End Sub

This has just been quickly thrown together, so I apologise for it not being well coded.

Dom

 

[fuunstatic]

Hi ManxDom

I tried it out and can see the potential of this code

however this was the result I got

johnsmith,sdjfsdj,peterjones,shfhjsd,johndoe,sdjfhdjkfhsdj,janedoe,shfjsdfhjsd

ie all the "CN=" and "OU=" were removed but it left in the names plus other data ie dept/div/office/country which I don't want

more help please.....thanks

&quot;Work to live, don't live to work&quot;

&quot;The problem with troubleshooting is that sometimes it shoots back&quot;

 

[bluedragon2]

=MID(a1,4,find(",",a1)-3)

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[fuunstatic]

->blue dragon... almost but the function only works once in the cell

It needs to continue throughout the cell and find the rest
I was thinking of something like

eg Cell D8 would contain this
=FIND("CN=",A8)
with additional cells to its right repeating the process from where the previous function left off
Cell E8
=FIND("CN=",$A8,(D8+($D8)))
Cell F8
=FIND("CN=",$A8,(E8+($D8)))

I get a series of numbers representing positions for this...

full data structure is as follows (for 1 cell)

CN=johnsmith,OU=marketing dept,OU=Manchester,OU=UK
CN=peterjones,OU=graphics dept,OU=London,OU=UK
CN=johndoe,OU=marketing dept,OU=London,OU=UK
CN=janedoe,OU=marketing dept,OU=Newbury,OU=UK

thanks

&quot;Work to live, don't live to work&quot;

&quot;The problem with troubleshooting is that sometimes it shoots back&quot;

 

[cheerio]

Public Function ExtractNames(origdata As String) As String
Dim answer As String
Dim temp As String
Dim startposn As Long
Dim endposn As Long

temp = origdata
answer = ""

Do While Len(temp) > 0
    startposn = InStr(1, temp, "CN", vbTextCompare)
    If startposn > 0 Then
        temp = Right(temp, Len(temp) - startposn - 2)
        endposn = InStr(1, temp, "OU", vbTextCompare)
        If endposn = 0 Then
            answer = answer & ", " & temp
            temp = ""
        Else
            answer = answer & Left(temp, endposn - 1)
            temp = Right(temp, Len(temp) - endposn - 2)
            If Left(temp, 1) = "," Then
                temp = Right(temp, Len(temp) - 1)
            End If
        End If
    Else
        temp = ""
    End If
Loop

ExtractNames = answer

End Function

Use ExtractNames as if it were a built in Excel function.

 

[fuunstatic]

-> Cheerio

spot on

thanks for that and have a Star on me

much appreciated

&quot;Work to live, don't live to work&quot;

&quot;The problem with troubleshooting is that sometimes it shoots back&quot;

 

[ManxDom]

try changing the line in my code that reads

If Right(currstr, 3) = "CN=" Or Right(currstr, 3) = "OU=" Then

to

If Right(currstr, 3) = "CN=" Then

I misunderstood your requirements in your first post

Dom

 

[fuunstatic]

->ManxDom

sorry if I wasn't clear enough - was trying to be as brief as possible

that works as well. In fact it worked better than the first which had alittle bug in it which i'm sure requires a little debugging to get it working

I've turned this into a function and using this so a star for you too

thanks very much

&quot;Work to live, don't live to work&quot;

&quot;The problem with troubleshooting is that sometimes it shoots back&quot;