Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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!
Reply With Quote
  #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.
Reply With Quote
  #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.
Reply With Quote
  #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.
Reply With Quote
  #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.
Reply With Quote
  #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..
Reply With Quote
  #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)
Reply With Quote
  #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
Reply With Quote
  #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.
Reply With Quote
  #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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 12:39 PM.


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