Find specific text and substitute characters

I've attached a sample spreadsheet. In Column C the alpha prefixes will be followed by policy numbers. The alpha prefixes can be several different policy types (LX, OX, LCLP, GUAR, etc.) I want to find only those cells that begin with either "LX" or "OX" and substitute "LXOR" for LX and "OXOR" for OX followed by the policy number. NOTE: Column D has been added to show what the prefix/policy number will look like after substitution.

For example: LX123456789 will now become LXOR123456789

The number of rows in Column C will vary from spreadsheet to spreadsheet.

You can use this formula in D7:

=IF(OR(LEFT(C7,2)="LX",LEFT(C7,2)="OX"),SUBSTITUTE(SUBSTITUTE(C7,"LX","LXOR"),"OX","OXOR"),C7)

then copy down.

Hope this helps.

Pete

That would work, however, I'm wanting to include this in a macro I've already written. The macro includes adding up multiple rows into one total sum for that policy, some sorting and formatting of data, etc.) I would like to include the substitition so that I can do everything without the user having to touch the spreadsheet. Is there a way to incorporate the commands below into vba code to use within my macro?

VBA
Once prefix has been changed, no more change will be made.

Please Login or Register  to view this content.

Thank you very much - I tested this and it replaced each line in Column C except the 1st row even though it met the criteria. I couldn't tell by your code what would have to change so it doesn't skip the first row in the column?

The code is written based on the data layout in your uploaded file.
Try change to

Please Login or Register  to view this content.

Otherwise, I need to see actual data layout.

That was about the only thing I could think of to change, and that worked perfectly. Thank you so much for taking the time to respond to my inquiry.

You are welcome and thanks for the rep.