Find and replace multiple values based on criteria

Hi

I have a column of 600+ values (English football positions) and would like to find and replace them based on some criteria. An example spreadsheet is attached. Example.xlsx

The column is something like:

Position
FW
FW
GK
M(C)
M(C)
D(C)
M(CLR),FW
AM(CL),FW
FW
D(R),DM(C)
M(CL)
D(CR)
DM(C),M(L)
M(CL)
....

The criteria I have is that any value with "M" (i.e. M(C), M(CL), DM(C)) should be replaced by "MF". Any value with "D" (i.e. D(R), D(C)) should be replaced with "DF".
[Note here, that DM(C) contains both "D" and "M" but "M" condition to take priority]
FW remains as FW.
GK remains as GK.
For cells with two values, both values should be replaced based on the criteria and remain separated by a comma.
E.g. D(R), DM(C) should be replaced by DF,MF
But, if the two values overlap, they should be replaced by just one.
E.g. DM(C),M(L) should be replaced by MF

This is a bit out of my league. Is there any easy way to do this?

Thank you

Hi Samuelhzb,
Just filter the criteria which you have in "position "coloum. and manually we can change this easily.find the attachment .

You might want to split POSITION into two columns first by selecting column F and using DATA-TOOLS-Text To Columns. Use a comma delimiter. In column H use this formula:

=IF(LEFT(F2,1)="M",REPLACE(F2,1,1,"MF"),IF(LEFT(F2,1)="D",REPLACE(F2,1,1,"DF"),F2))

Copy the formula to column I as well. You can concatenate the columns after this process if it's important for you to keep all the info in one cell rather than multiple cells for each position.

After looking at your other criteria I think this formula might actually be more accurate.

IF(LEFT(F2,1)="M",REPLACE(F2,1,8,"MF"),IF(LEFT(F2,1)="D",REPLACE(F2,1,8,"DF"),F2))

Initially I thought you only wanted to replace the M or F while keeping the remainder of the text.

Hi Bob,

Your formula works well except for values like AM(LR) and AM(RLC) which I would like to convert to MF too (since it contains a M).
There does not seem to be a way to work round your formula for this?

And Concatenate will work if values for the two columns are different. But if the values in the two columns are the same (e.g. MF and MF), how can I make it to recognise that and return MF instead of MF,MF?

Maybe in column H…

=IF(OR(LEFT(F2,1)="M",LEFT(F2,2)="AM"),REPLACE(F2,1,8,"MF"),IF(LEFT(F2,1)="D",REPLACE(F2,1,8,"DF"),F2))

then in column I

=IF(OR(LEFT(G2,1)="M",LEFT(G2,2)="AM"),REPLACE(G2,1,8,"MF"),IF(LEFT(G2,1)="D",REPLACE(G2,1,8,"DF"),""))

Concatinate in J

=IF(OR(H2=I2,I2=0),H2,IF(I2<>"",CONCATENATE(H2,", ",I2),H2))

Also looks like newdovermans solution works