Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old September 17th, 2009, 03:54 PM
Registered User
 
Join Date: Sep 2009
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Remove First and Last Characters from String if it Contains Those Characters

I'm trying to remove a [ and ] from the beginning and end of some cell values in a specific column. I want to check each cell to see if it contains those characters and remove them if it does.

Thanks for the help!
  #2 (permalink)  
Old September 17th, 2009, 05:02 PM
Authorized User
Points: 193, Level: 3
Points: 193, Level: 3 Points: 193, Level: 3 Points: 193, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2007
Location: North East Pennsylvania, USA.
Posts: 48
Thanks: 0
Thanked 4 Times in 4 Posts
Default

DoriftuEvo,

Please list about 20 of the items in the column.


Have a great day,
Stan
__________________
stanleydgromjr

Windows 8.1, Excel 2007.
  #3 (permalink)  
Old September 17th, 2009, 06:07 PM
Registered User
 
Join Date: Sep 2009
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

2.00657892
3.10558234
[10.2345342]
[14.2560234]
4.13423105
5.14613461
4.23123451


2.23453253
[12.3452352]
[15.1624525]
[14.2145324]


Here's an example of what the column looks like. I left blank rows on purpose because some cells are blank.

Thanks for the quick response.
  #4 (permalink)  
Old September 17th, 2009, 11:02 PM
Authorized User
Points: 193, Level: 3
Points: 193, Level: 3 Points: 193, Level: 3 Points: 193, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2007
Location: North East Pennsylvania, USA.
Posts: 48
Thanks: 0
Thanked 4 Times in 4 Posts
Default

DoriftuEvo,

If your posted data is in column A, beginning in row 1 (before the macro):
2.00657892
3.10558234
[10.2345342]
[14.2560234]
4.13423105
5.14613461
4.23123451

2.23453253
[12.3452352]
[15.1624525]
[14.2145324]


After the macro:
2.00657892
3.10558234
10.2345342
14.2560234
4.13423105
5.14613461
4.23123451

2.23453253
12.3452352
15.1624525
14.2145324



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel


Code:
 
Option Explicit
Sub RemoveSquareBrackets()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  If Left(c, 1) = "[" Then c = Mid(c, 2, Len(c) - 1)
  If Right(c, 1) = "]" Then c = Left(c, Len(c) - 1)
Next c
Application.ScreenUpdating = True
End Sub

Then run the "RemoveSquareBrackets" macro.
__________________
stanleydgromjr

Windows 8.1, Excel 2007.
  #5 (permalink)  
Old September 18th, 2009, 09:02 AM
JP JP is offline
Authorized User
Points: 199, Level: 3
Points: 199, Level: 3 Points: 199, Level: 3 Points: 199, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2008
Location: NYC, NY, USA.
Posts: 57
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Try this:

Code:
 
Sub RemoveBrackets()
Dim arrData() As Variant
Dim arrReturnData() As Variant
Dim rng As Excel.Range
Dim lRows As Long
Dim lCols As Long
Dim i As Long, j As Long
  lRows = Selection.Rows.Count
  lCols = Selection.Columns.Count
  ReDim arrData(1 To lRows, 1 To lCols)
  ReDim arrReturnData(1 To lRows, 1 To lCols)
  Set rng = Selection
  arrData = rng.Value
  For j = 1 To lCols
    For i = 1 To lRows
      If Left$(arrData(i, j), 1) = "[" And _
         Right$(arrData(i, j), 1) = "]" Then
        arrReturnData(i, j) = Mid$(arrData(i, j), 2, Len(arrData(i, j)) - 2)
      Else
        arrReturnData(i, j) = arrData(i, j)
      End If
    Next i
  Next j
  rng.Value = arrReturnData
End Sub
--JP


Quote:
Originally Posted by DoriftuEvo View Post
2.00657892
3.10558234
[10.2345342]
[14.2560234]
4.13423105
5.14613461
4.23123451


2.23453253
[12.3452352]
[15.1624525]
[14.2145324]


Here's an example of what the column looks like. I left blank rows on purpose because some cells are blank.

Thanks for the quick response.
  #6 (permalink)  
Old September 20th, 2009, 05:34 AM
Registered User
 
Join Date: Sep 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to gluefish
Default Getting rid of the brackets.

You want to get rid of the brackets.

You could use the instructions stanleydgromjr (above) used to install a macro.
Personally, I don't bother with the macros, just put a sub in the code page using alt-F11, to access it. To run it, place the cursor in the code and hit F5.

Everyone else but me seems to think it was necessary to just take the brackets from the left and right side of your numbers. Looking at it, it appeared you just wanted plain numbers, and the place of the brackets didn't matter.

Also, I think setting the range unnecessarily complicates matters when you can just make a loop that uses cell references.

Try this: assuming your data is from A1 to A100:

Code:
Sub removeBrackets()
    Dim r
    For r = 1 To 100
        Cells(r, 1) = Replace(Cells(r, 1), "[", "")
        Cells(r, 1) = Replace(Cells(r, 1), "]", "")
    Next
End Sub
(That left me with:)
2.00657892
3.10558234
10.2345342
14.2560234
4.13423105
5.14613461
4.23123451

2.23453253
12.3452352
15.1624525
14.2145324


Quote:
Originally Posted by DoriftuEvo View Post
I'm trying to remove a [ and ] from the beginning and end of some cell values in a specific column. I want to check each cell to see if it contains those characters and remove them if it does.

Thanks for the help!

Last edited by gluefish; September 20th, 2009 at 05:44 AM..
  #7 (permalink)  
Old September 20th, 2009, 07:09 AM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Just in case if you are not worried about the position of the brackets (first, last etc), you might find the following faster:

Range("A1:A100").Replace "[", ""
Range("A1:A100").Replace "]", ""

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
  #8 (permalink)  
Old September 20th, 2009, 11:38 AM
Registered User
 
Join Date: Sep 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to gluefish
Default Making it easier yet...

Next we get to look at: How did the numbers get there in the first place?
Look at the process you used to write those values.
Was it in a script? I'd suggest adding something like this into your script:

Assuming your script is working with some value numberWorkingWith from somewhere. Sometimes it picks up a number with brackets, sometimes not.
Assume that you're incrementing nextRow after each write, and you have a fixed column numberColumn.
The idea is to strip the brackets before writing them to the column. That way you never have to go run a macro or a sub - the numbers will automagically be the way you want them - you only have to do this ONCE, in the feed script, not every time you repeat the process of writing this set of values to the column.

Code:
....
nValueToWrite = replace(numberWorkingWith, "]", "")
nValueToWrite = replace(numberWorkingWith, "[", "")
cell(nextRow, numberColumn) = nValueToWrite

Quote:
Originally Posted by Shasur View Post
Just in case if you are not worried about the position of the brackets (first, last etc), you might find the following faster:

Range("A1:A100").Replace "[", ""
Range("A1:A100").Replace "]", ""

Cheers
Shasur
  #9 (permalink)  
Old September 21st, 2009, 12:56 PM
Registered User
 
Join Date: Sep 2009
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Wow, thanks a lot for all the info! I will be testing these tomorrow.
  #10 (permalink)  
Old September 22nd, 2009, 09:01 AM
Registered User
 
Join Date: Sep 2009
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by gluefish View Post
You want to get rid of the brackets.

You could use the instructions stanleydgromjr (above) used to install a macro.
Personally, I don't bother with the macros, just put a sub in the code page using alt-F11, to access it. To run it, place the cursor in the code and hit F5.

Everyone else but me seems to think it was necessary to just take the brackets from the left and right side of your numbers. Looking at it, it appeared you just wanted plain numbers, and the place of the brackets didn't matter.

Also, I think setting the range unnecessarily complicates matters when you can just make a loop that uses cell references.

Try this: assuming your data is from A1 to A100:

Code:
Sub removeBrackets()
    Dim r
    For r = 1 To 100
        Cells(r, 1) = Replace(Cells(r, 1), "[", "")
        Cells(r, 1) = Replace(Cells(r, 1), "]", "")
    Next
End Sub
(That left me with:)
2.00657892
3.10558234
10.2345342
14.2560234
4.13423105
5.14613461
4.23123451

2.23453253
12.3452352
15.1624525
14.2145324
Thanks, that worked just fine.

The brackets got there because I put them around all values over 10 in order to omit them from a graph I was looking at (I'm sure you guys would have a better solution for that as well). I then decided I wanted to look at the graph with all the numbers and didn't want to manually remove the brackets from all 837 rows.


Similar Threads
Thread Thread Starter Forum Replies Last Post
REMOVE leading zero and alpha characters in string in XSL... san_yacha XSLT 3 July 28th, 2009 04:06 AM
REMOVE leading zero and alpha characters in string in XSL... san_yacha BOOK: XSLT Programmer's Reference, 2nd Edition 0 July 28th, 2009 01:30 AM
code to remove ms word special characters vikaspa Pro PHP 0 November 27th, 2008 10:20 PM
remove characters from "=" and before ymeyaw Excel VBA 2 February 27th, 2007 06:02 AM
Custom script to remove special characters midoriweb Classic ASP Basics 2 January 12th, 2007 08:05 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.