We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Strip out characters from a string in Excel using VBA

markholmes24
markholmes24 asked
on
Medium Priority
13,814 Views
Last Modified: 2008-02-26
I need some VBA code which wll remove certain characters from a string.

At the moment, my script reads values from the 2nd column, adds the prefix DL_ to the string and write's it to the frst column.  The string it reads is actually a path, eg E:\Datalibrary\britain\bes565 - it then output's this to the first column as DL_E:\Datalibrary\britain\bes565.

What I basically need to do is before the string gets written to the first col, strip out the E: and replace any \ with _ .

The spreadsheet once finished is going to be used to feed a VBScript which creates AD groups and assgns permissions to folders automatically, based on the data it reads from the 3 columns of the spreadsheet.

Any help greatly appreciated.

Regards,

Mark


My code:-

Private Sub CommandButton1_Click()

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet

'--- set a reference to Excel
Set objXL = New Excel.Application
'--- open the workbook
Set objWkb = objXL.Workbooks.Open("c:\groupsunformatted.xls")
Set objSht = objWkb.Worksheets("Grouplist")


Dim iRow As Integer
Dim strCol1 As String
Dim strCol2 As String

'Start reading from row 1
iRow = 2
Do While True
   
     strCol1 = objSht.Cells(iRow, 1).Value
 strCol2 = objSht.Cells(iRow, 2).Value
 'strCol3 = objSht.Cells(iRow, 3).Value
     '--- if the name of the field is "end of list" then exit the loop
     If strCol1 = "endoflist" Then
          Exit Do
     End If
     


'objSht.Cells(iRow, 6) = strField

ActiveSheet.Cells(iRow, 1).Value = "DL_" & strCol1  'write's suggested group name to col1, prefixing it with DL_
ActiveSheet.Cells(iRow, 2).Value = strCol1
ActiveSheet.Cells(iRow, 3).Value = strCol2


     'move down one row
     
     iRow = iRow + 1
Loop
 
  objWkb.Close
 
End Sub
Comment
Watch Question

try this change:

Private Sub CommandButton1_Click()

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet

'--- set a reference to Excel
Set objXL = New Excel.Application
'--- open the workbook
Set objWkb = objXL.Workbooks.Open("c:\groupsunformatted.xls")
Set objSht = objWkb.Worksheets("Grouplist")


Dim iRow As Integer
Dim strCol1 As String
Dim strCol2 As String

'Start reading from row 1
iRow = 2
Do While True
   
     strCol1 = objSht.Cells(iRow, 1).Value
 strCol2 = objSht.Cells(iRow, 2).Value
 'strCol3 = objSht.Cells(iRow, 3).Value
     '--- if the name of the field is "end of list" then exit the loop
     If strCol1 = "endoflist" Then
          Exit Do
     End If


'objSht.Cells(iRow, 6) = strField

ActiveSheet.Cells(iRow, 1).Value = "DL_" &Replace(Replace(strCol1, "E:", ""), "\", "_") 'write's suggested group name to col1, prefixing it with DL_ <<<<<MAKE THIS CHANGE IN THIS ONE LINE

ActiveSheet.Cells(iRow, 2).Value = strCol1
ActiveSheet.Cells(iRow, 3).Value = strCol2


     'move down one row
     
     iRow = iRow + 1
Loop
 
  objWkb.Close
 
End Sub

AW

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Commented:
Hi markholmes24,
I didn't use a double replace because I wasn't sure that it would always be "E:". Anyway another alternative :)

...................
 strCol1 = FxStr(objSht.Cells(iRow, 1).Value)
 strCol2 = FxStr(objSht.Cells(iRow, 2).Value)
.....................

Public Function FxStr(iVal) As String
    Dim mTemp$, mCnt&
    mTemp = Replace(CStr(iVal), "\", "_")
    mCnt = InStr(1, mTemp, ":") - 1
    FxStr = Left(mTemp, mCnt - 1) & Mid(mTemp, mCnt + 2)
End Function

dragontooth

Author

Commented:
Thanks both, I'll split the points.

How would I trim the _ off the end of the striing? - i tried using Left,but it didn't work.
If the _ is the very last character on the right end of the string:

MyString = Left(MyString , Len(MyString) - 1)  <<< gives you everything to the left of the last character in the string

AW

Author

Commented:
I'm still getting 'invalid procedure call or arguement' when I run this sub - looks like the problem is with line strGroupname = Left(strGroupname, Len(strGroupname) - 1).  Any ideas?


Private Sub CommandButton1_Click()

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet

'--- set a reference to Excel
Set objXL = New Excel.Application
'--- open the workbook
Set objWkb = objXL.Workbooks.Open("c:\groupsunformatted.xls")
'--- set a reference to the correct tab (I always name my worksheet tabs, rather than leave the default)
Set objSht = objWkb.Worksheets("Grouplist")


Dim iRow As Integer
Dim strCol1 As String
Dim strCol2 As String
Dim strGroupname As String
Dim strGroupnameF As String
'Start reading from row 1
iRow = 2
Do While True
     '--- read the name of the field (which is in the 1st column)
     strCol1 = objSht.Cells(iRow, 1).Value
 strCol2 = objSht.Cells(iRow, 2).Value
 'strCol3 = objSht.Cells(iRow, 3).Value
     '--- if the name of the field is "end of list" then exit the loop
     If strCol1 = "endoflist" Then
          Exit Do
     End If



'objSht.Cells(iRow, 6) = strField
strGroupname = strCol1
strGroupname = Replace(Replace(strGroupname, "E:", ""), "\", "_")
strGroupname = Left(strGroupname, Len(strGroupname) - 1)
ActiveSheet.Cells(iRow, 1).Value = "DL" &strGroupname
ActiveSheet.Cells(iRow, 2).Value = strCol1
ActiveSheet.Cells(iRow, 3).Value = strCol2
'ActiveSheet.Cells(iRow, 4).Value = strCol3



     '--- move down one row
     
     iRow = iRow + 1
Loop
 
  objWkb.Close
 
End Sub
CERTIFIED EXPERT

Commented:
Maybe because Len(strGroupname) = 1 or 0

Try
if Right(strGroupname,1) = "_" Then strGroupname = Left(strGroupname, Len(strGroupname) - 1)

dragontooth


Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.