Find/Replace JUST the second set of []

[USMCFROSTY]

I want to replace JUST the second set
Ex:
[Mr] usmc [123]

It should look like this
[Mr] usmc 123

I have 600 of them so normal procedure didnt seem right.

 

[stopher]

you could probably write a formula but it would be long and messy.

Try this function:

Public Function Rmv2ndSqrs(sInput As String) As String

Dim iSQ_open_pos As Integer
Dim iSQ_close_pos As Integer
Dim sCleaned As String

'find position of first set of brackets
iSQ_open_pos = InStr(sInput, "[")
iSQ_close_pos = InStr(sInput, "]")

'if we have first set of brackets then we can remove any further brackets
If iSQ_open_pos > 0 And iSQ_close_pos > 0 Then
    sCleaned = Replace(sInput, "[", "", iSQ_close_pos + 1)
    sCleaned = Replace(sCleaned, "]", "")
    Rmv2ndSqrs = Left(sInput, iSQ_close_pos) & sCleaned
Else
    Rmv2ndSqrs = sInput
End If

End Function

Once it has found a first pair of brackets it will remove any further square brackets regardless of whether they are paired or not.

hth
Chris

 

[HiArt]

or.....

=SUBSTITUTE(SUBSTITUTE(A1,"[","",2),"]","",2)

Firstly you need the input to be consistent.

Substitutes new_text for old_text in a text string using the format: SUBSTITUTE(text,old_text,new_text,instance_num)

=SUBSTITUTE(SUBSTITUTE(A1,"[","",2),"]","",2)

SUBSTITUTE(A1,"[","",2) Looks in cell A1 (replace as required) to replace "[" with "", instance_num is set to 2, so only the second instance is changed. So using your example "[Mr] usmc [123]" has become "[Mr] usmc 123]"

we now use this string in the second
=SUBSTITUTE(SUBSTITUTE(A1,"[","",2),"]","",2)
which has effectively become =SUBSTITUTE("[Mr] usmc 123]","]","",2).

This replaces the 2nd instance of "]" with "" leaving the output as "[Mr] usmc 123".

HTH

 

[namliam]

or.....

=SUBSTITUTE(SUBSTITUTE(A1,"[","",2),"]","",2)

I have never seen this function, where is this hiding? Is this possibly in a new version? I am using 2002.

 

[stopher]

I have never seen this function, where is this hiding? Is this possibly in a new version? I am using 2002.

I was wondering that. Then I realised this is an Excel thread. Doh!

Shame Substitute isn't available in Access.

I guess my function will still work (?) but HiArt's is more elegant.

Chris

 

[namliam]

I was wondering that. Then I realised this is an Excel thread. Doh!

*UGH*

Excel thread... course... *hits self upside the head*