Find and replace question

[Barryj]

I have data that is copied form a web based program and pasted into a excel spread sheet for printing.

The data is reduced with some find and replace code before printing, my problem that I am trying to solve is some of the data comes in the format of for example [Sick Leave] which is then replaced with Sick Leave.

Is there a find and replace code that can remove the [ ] brackets from any data that exists on the sheet.

Below is part of the code I am using to acheive results currently, I am hoping to have a bit of code that can remove the [ ] brackets and leave the Sick Leave.


Selection.Replace What:="[Sick Leave]", Replacement:="Sick Leave", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


Thanks for any advice

[ashleyuk1984]

You could do two find and replaces.

Original Cell value = [Sick Leave]


Cells.Replace What:="[", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

This would leave you with = Sick Leave]

Then do another replace, but this time use ]


Cells.Replace What:="]", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

This would leave you with = Sick Leave

This would work with any cells that contained [ & ]

[Barryj]

Thanks for looking at this ashleyuk1984, I am trying to find a solution that removes both brackets regardless of the data between the brackets because sometimes there is unknown data being pasted into the worksheet, I understand your solution works but only for known data.

[Paul_Hossler]

Option Explicit
Sub test()
Dim rCell As Range
With ActiveSheet

'for testing
.Cells(1, 1).Value = "[sick leave]"
.Cells(2, 1).Value = "[SICK leave]"
.Cells(3, 1).Value = "Sick leave"
.Cells(4, 1).Value = "[not sick leave]"
.Cells(5, 1).Value = "[sick leave"
.Cells(6, 1).Value = "sick leave]"
.Cells(7, 1).Value = "[something else]"

.Cells(1, 1).CurrentRegion.Select
'example
If Not TypeOf Selection Is Range Then Exit Sub

For Each rCell In Selection.Cells
'To match the special characters left bracket ([), question mark (?), number sign (#), and asterisk (*),
'enclose them in brackets. The right bracket (]) can't be used within a group to match itself, but it
'can be used outside a group as an individual character.
If rCell.Value Like "[[]*]" Then
rCell.Value = Mid(rCell.Value, 2, Len(rCell.Value) - 2)
End If
Next
End With
End Sub



This approach is a little brute force, but seems to work as I understand the requirements

Paul

[ashleyuk1984]

I'm not following?
My solution removes the brackets regardless of what's inbetween them.

For example, if you have = [value]
Then you will be left with = value

If you have = [78nc347c34y7mc]
You will be left with = 78nc347c34y7mc

This is what you want surely ??

[Barryj]

ashleyuk1984, my mistake your advice works as you pointed out, I was altering the wrong code linked to a button, thank you for your assistance, thank you also Paul_Hossler for your time looking at this. I will mark this as solved.

[Paul_Hossler]

my mistake your advice works as you pointed out, I was altering the wrong code linked to a button, thank you for your assistance, thank you also Paul_Hossler for your time looking at this. I will mark this as solved.


Glad you have it

I think that ashleyuk1984's will also remove any brackets inside the text, and well as any mis-matched brackets:

Case [1] here
Case 2] here
Case [3 here
[Case 4] here
Case 5 here]


etc.

Paul