Manipulate strings within an Excel cell

[NeoTheNerd]

Hi All, I would like to build a list within a single cell from a sting stored another cell. The string has a semi colon delimiter ';' to separate the string. I need to extract the CN=*, and remove the remainder, in the adjacent cell.

I've tried the following, but with little joy

=SUBSTITUTE(A87,";CN=",CHAR(10))
=SUBSTITUTE(A87,",",CHAR(10))
=SUBSTITUTE(A87,",",CHAR(10)&",CN=")
=SUBSTITUTE(A87,";",CHAR(10))

How do I keep and list all the CN= entries and remove the remainder?

CN=User2,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=User4,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=User56,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=User9,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=Jane45 user,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=User-Donna,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=User76 smith,OU=blurb,OU=Test4,DC=Test,DC=Testal;CN=Pink Panther,OU=blurb,OU=Test,DC=Testing,DC=Testal;CN=Testuser78,OU=blurb,OU=Tester,DC=Test,DC=Testal;CN=great Scott,OU=blurb,OU=Test,DC=Test,DC=Local;CN=Leah Human,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=Alan Desai,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=Duff Beer,OU=Groups,OU=Test,DC=Test,DC=Testal;CN=Jane Doe,OU=Users,OU=Test76,DC=Test,DC=Testal;CN=simple user67,OU=Users,OU=Test,DC=Test,DC=Testal;CN=test O'Lord,OU=Users,OU=Test,DC=Concero,DC=Testal

CN=User1 CN=User2 CN=User3 etc...

 

[Peter_SSs]

Does this user-defined function do what you want? To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function GetParts(s As String, Optional PartPrefix As String = "CN") As String
  GetParts = Replace(Join(Filter(Split(Replace(Replace("," & s, ";", ","), "," & PartPrefix, ",|" & PartPrefix), ","), "|"), ","), "|", "")
End Function

The standard function extracts the 'CN' parts as shown in column B. If you wanted other parts of the text extracted you can specify a second argument to the function as shown in column C.

NeoTheNerd.xlsm
	A	B	C
1	CN=User2,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=User4,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=User56,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=User9,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=Jane45 user,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=User-Donna,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=User76 smith,OU=blurb,OU=Test4,DC=Test,DC=Testal;CN=Pink Panther,OU=blurb,OU=Test,DC=Testing,DC=Testal;CN=Testuser78,OU=blurb,OU=Tester,DC=Test,DC=Testal;CN=great Scott,OU=blurb,OU=Test,DC=Test,DC=Local;CN=Leah Human,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=Alan Desai,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=Duff Beer,OU=Groups,OU=Test,DC=Test,DC=Testal;CN=Jane Doe,OU=Users,OU=Test76,DC=Test,DC=Testal;CN=simple user67,OU=Users,OU=Test,DC=Test,DC=Testal;CN=test O'Lord,OU=Users,OU=Test,DC=Concero,DC=Testal	CN=User2,CN=User4,CN=User56,CN=User9,CN=Jane45 user,CN=User-Donna,CN=User76 smith,CN=Pink Panther,CN=Testuser78,CN=great Scott,CN=Leah Human,CN=Alan Desai,CN=Duff Beer,CN=Jane Doe,CN=simple user67,CN=test O'Lord	DC=Test,DC=Testal,DC=Test,DC=Testal,DC=Test,DC=Testal,DC=Test,DC=Testal,DC=Test,DC=Testal,DC=Test,DC=Testal,DC=Test,DC=Testal,DC=Testing,DC=Testal,DC=Test,DC=Testal,DC=Test,DC=Local,DC=Test,DC=Testal,DC=Test,DC=Testal,DC=Test,DC=Testal,DC=Test,DC=Testal,DC=Test,DC=Testal,DC=Concero,DC=Testal
CN

Cell Formulas
Range	Formula
B1	B1	=GetParts(A1)
C1	C1	=GetParts(A1,"DC")

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Remove parts of a string ( ';' Semi-Colon Delimiter) and listing the remainder of the string in one cell
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[NeoTheNerd]

Hi Peter, I tried the function, but came back with a #Name? error.
is vba similar to vb.net?

 

[NeoTheNerd]

Should the function be something like this? Not that it works yet.

Sub testC()

Dim LongString As String
Dim StringDelim() As String
Dim SubStringRemove1 As String
Dim SubStringRemove2 As String
Dim SubStringRemoveOU As String
Dim SubStringRemoveDC As String
Dim NewString As String

LongString = ("CN=User2,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=User4,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=User56,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=User9,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=Jane45 user,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=User-Donna,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=User76 smith,OU=blurb,OU=Test4,DC=Test,DC=Testal;CN=Pink Panther,OU=blurb,OU=Test,DC=Testing,DC=Testal;CN=Testuser78,OU=blurb,OU=Tester,DC=Test,DC=Testal;CN=great Scott,OU=blurb,OU=Test,DC=Test,DC=Local;CN=Leah Human,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=Alan Desai,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=Duff Beer,OU=Groups,OU=Test,DC=Test,DC=Testal;CN=Jane Doe,OU=Users,OU=Test76,DC=Test,DC=Testal;CN=simple user67,OU=Users,OU=Test,DC=Test,DC=Testal;CN=test O'Lord,OU=Users,OU=Test,DC=Concero,DC=Testal")
SubStringRemove1 = (",OU=*,")
SubStringRemove2 = (",DC=*,")

SubStringRemoveOU = Replace(LongString, ",OU=*,", "")
SubStringRemoveDC = Replace(SubStringRemoveOU, SubStringRemove2, "")
NewString = SubStringRemoveDC

StringDelim = Split(NewString, "; Chr(10)")

MsgBox StringDelim(0)

End Sub

 

[Peter_SSs]

I tried the function, but came back with a #Name? error.

Rename your module so that it has a different name to the function.