Remove First and Last Characters from String if it Contains Those Characters

DoriftuEvo · September 17th, 2009, 03:54 PM

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!

stanleydgromjr · September 17th, 2009, 05:02 PM

DoriftuEvo,

Please list about 20 of the items in the column.

Have a great day,
Stan

DoriftuEvo · September 17th, 2009, 06:07 PM

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.

stanleydgromjr · September 17th, 2009, 11:02 PM

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.

JP · September 18th, 2009, 09:02 AM

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

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.

gluefish · September 20th, 2009, 05:34 AM

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

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!

Shasur · September 20th, 2009, 07:09 AM

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

gluefish · September 20th, 2009, 11:38 AM

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

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

DoriftuEvo · September 21st, 2009, 12:56 PM

Wow, thanks a lot for all the info! I will be testing these tomorrow.

DoriftuEvo · September 22nd, 2009, 09:01 AM

Quote:

Originally Posted by gluefish

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.