Help With IF or A Macro

[Dazzawm]

I have in column 'B, cells that have either "With Manual Gearbox" or "With Auto Gearbox" and I want to make these in column 'F' MTM or ATM. I have tried an IF formula which works but only when those words are in the cell. What do I do if there are other words or numbers in the cell is there a macro that will do it? I cant sort alphabetically because like I say there may be other things within the cells and it will stretch down 10000 rows.

 

[bolo]

I have in column 'B, cells that have either "With Manual Gearbox" or "With Auto Gearbox" and I want to make these in column 'F' MTM or ATM. I have tried an IF formula which works but only when those words are in the cell. What do I do if there are other words or numbers in the cell is there a macro that will do it? I cant sort alphabetically because like I say there may be other things within the cells and it will stretch down 10000 rows.

try this code.

=if(len($b2)-len(substitute(Proper($b2),"Manual",""))>0,"MTM",
if(len($b2)-len(substitute(Proper($b2),"Auto",""))>0,"MTM",""))

Regards

Bolo

 

[ukmikeb]

Hi

Maybe this in F2 -

=IF(ISERROR(SEARCH("Auto Gearbox",$B2)),IF(ISERROR(SEARCH("Manual Gearbox",$B2)),"","MTM"),"ATM")

and copied down

hth

 

[Dazzawm]

Thanks, both did what I needed.