How to "clean" some text data?

[Eloise T]

I have a macro line or two (see below) that needs to reduce any of the following...

from:
oss
nn INCH OSS
nn IN OSS
nn" OSS
OSS nn"

where nn = any two-digit number

to simply:
OSS

I've tried using the following 3 lines:
.Replace "oss", "OSS", xlWhole ...which works with [oss to OSS]
.Replace "**OSS ?? in", "OSS", xlPart ...which works with [OSS 65 in to OSS]
.Replace "?? ** OSS", "OSS", xlPart ...which also works with [65 IN OSS to OSS]

or to put it another way ...it corrects:
oss
nn INCH OSS
nn IN OSS

but does not correct:
nn" OSS
OSS nn"

I'm pulling my hair out! Please help before it's all gone...

Thanks in advance.

 

[Marc L]

Without again any attachment maybe .Replace "*oss*", "OSS", xlPart …​

​

 

[Eloise T]

The reason I'm being careful and not using something all=encompassing is because I have data that reads "OSS - 2 Trips" that I don't want to "clean."


So far, the only one I'm having trouble cleaning using the following 3 lines:

.Replace "oss", "OSS", xlWhole

.Replace "OSS*in*", "OSS", xlPart

.Replace "** OSS", "OSS", xlPart
is:
OSS nn" (where nn is a 2-digit number)

My problem seems to be isolating/distinguishing the quotation marks since quotation marks are used in the .Replace statement.

.Replace "OSS nn" + ""","OSS",xlPart
doesn't work.

UPDATE: this worked for OSS 65" ! .Replace "OSS ???", "OSS", xlPart

Below is the list. It all gets cleaned except for OSS 65" (the 2nd one in the list)

OSS 65 IN

OSS 65" Now works with UPDATE above. THANK YOU Mark L for your suggestion which made me think outside the box.

OSS 65 INCH

OSS 65IN

OSS 65INCH

65" OSS

65 IN OSS

65IN OSS

65INCH OSS