Find Substring Cut and Paste to Beginning of Same Cell (Almost Works....)

So I have this code that is supposed to find a string that looks like this: "Op.123" Once it finds this string, it performs a cut/paste that puts the string at the beginning of the cell, adds a comma, and adds a space. For example, if you have the string: This is my Op.123 string" the code should change it to: "Op. 123, This is my string". The problem is, it only works when the string is at the beginning of the cell text but doesn't work anywhere else. So "Op. 123 string" works but "String Op.123" does not work. I am working on a Mac so any code needs to work with the Mac version of Excel. Here is the code that needs to be tweaked:

HTML Code: 

Thanks in advance. You guys are awesome

Here is a solution that uses regular expressions, which is a much more powerful pattern matching tool than Like and InStr. It allows you to find out if a pattern is present, capture it, and switch it around. I did some testing to make sure the concept works but without your file and data I can't test this exact code.

Please Login or Register  to view this content.

Regex is a pattern matching language. I will break down the pattern I used:

^(.*)(Op\.[0-9]*)(.*)$

^ matches the beginning of the line. It ensures that the pattern must start at the beginning of the line.

(.*) The . means "match any character". The * means "match any number of the preceding character" so it will match any string of characters. The ( ) mean "save whatever matches this".
(Op\.[0-9]*) This says look for Op. (you have to use the \ to mean "look for an actual . instead of matching any character") followed by any number of digits. Save whatever matches this.
(.*)$ The $ means "end of string" so this will match all characters following your Op string to the end of the line. Save whatever matches this.

Now we have saved three strings: The beginning the string, the Op.xxx string, and the end of the string. We can use the $ variables to rearrange them. Each string we saved can be referenced in Replace as $1, $2, and $3. So we replace the above string with

$2, $1$3

$2 is the Op string, followed by the original beginning of the string, and then the original end of the string.

Administrative Note:

Welcome to the forum.

Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here. I am, therefore, closing this thread, but you may continue here in the original thread