A Macro with Message Box to rearrange text in a cell

[Zyphon]

Hi Guys

I would like to know if there is a quick Macro I could use to rearrange text in a cell.

For example our organisation list all the departments in an unconsistant way and it makes finding the departments hard using A-Z.

For example some department will say "Department of ...", "Dept of ...", Division of ..." or "Div of ...". (Obviously excluding the quotes).

What I wanted to know is this. If you highlight the range you wish to work with, could you use a message box to ask you what text you wish to edit and then automatically search the range and make the changes.

For example search for "Dept of " (note I would need to keep the space after of otherwise I would have leading spaces) so that "Dept of ABC" becomes: "ABC, Dept of" and say "Division of XYZ" becomes "XYZ, Division of"

I would really appreciate the help of a VBA guru to assist me here as this would help me out greatly when search for departments in my drop down list.

Thanks in advance for any help offered.

[royUK]

Ctrl -> F on the worksheet will display the find & replace option

[Zyphon]

Ctrl -> F on the worksheet will display the find & replace option

Lol, yes I could use that the Ctrl F function, This would be fine for removing the "Dept of " etc, however I am confused as to how I could get that function to transpose the cell to move said "Dept of " from the beginning of the cell to the end of the cell so that we get the department name first followed by: ", Dept of".

There are over 700 departments so to manually use the CTRL-F function would be a painful excercise. If I were to use CTRL-F and used say: Find what: "Dept of ABC", Replace with: "ABC, Dept of" and did this individually I would take an age.

I was hoping a Macro would speed up the process.

[DonkeyOte]

Perhaps something along the lines of:

Please Login or Register  to view this content.

[Zyphon]

Hi DonkeyOte

Thank you so much for this piece of code it has worked perfectly and did exactly what I wanted. Thanks for your help, you are a genius.