• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find Everything In Paretheses, and Move To End Of Cell

I am seeking help with finding a two specific brackets "(" & ")", selecting everything between it (including parentheses), and placing it at the end of the cell it was found in.


For example: I need help with a code that will find:


"(Merry Christmas) We wish you a"


--and replace cell content text above to below--


"We wish you a (Merry Christmas)"
 
Assuming your text is in A1

=RIGHT(A1,LEN(A1)-FIND(")",A1))&" " &LEFT(A1,FIND(")",A1))
 
VBA:


Select a range and run the following code

[pre]
Code:
Sub replace_text()
Dim b1 As Integer
Dim b2 As Integer
Dim myRange As Range

Set myRange = Selection

For Each c In myRange
b1 = InStr(1, c.Text, "(")
b2 = InStr(1, c.Text, ")")

If b1 > 0 And b2 > 0 Then
c.Value = Trim(Right(c.Text, Len(c.Text) - b2) + " " + Left(c.Text, b2))
End If

Next
End Sub
[/pre]
 
Aw man that worked perfect!


Thanks Hui


Is there anyway you could tweak this same code to work in MS Word as well?


Or would it be too much trouble?
 
Indi

Have a read of:

http://office.microsoft.com/en-us/support/add-power-to-word-searches-with-regular-expressions-HA001087305.aspx?redir=0

I am sure that can help you
 
Back
Top