Excel formula help needed......

[Countcussy]

I have data in a column of cells in the following format...

3SV19T022M
3SV19F022M
3SV19R022M
3SV19N022M

What I need is a formula to return me a value, depending on the letter it finds in a cell.

If it finds T, I want it to return OV
If it finds F, i want it to return FL
If it finds R, i want it to return SPLIT
If it finds N, i want it to return 316

Using the IF command, I can get it to return 2 of the values, but I can`t figure out how to get more.

Help would be greatly appreicated.

 

[fullmetaltony]

=IF(ISERROR(FIND("T",A5))=FALSE,"OV",IF(ISERROR(FIND("F",A5))=FALSE,"FL",IF(ISERROR(FIND("R",A5))=FALSE,"SPLIT",IF(ISERROR(FIND("N",A5))=FALSE,"316",""))))

Should do it, A5, being the cell with the info being looked at

edit, should mention, it returns a blank cell if it finds none of the letters, not sure what it does if it finds more than one, but I assumed that wouldn't be a problem for you
edit, have discovered that it will return the value for T first, then F and then R if there are more than one of the letters being looked for. change the order of the nested IF's if you want to change the priority

p.s. remove any spaces in the formula the forum decided to chuck in

 

[Countcussy]

You sir are a GENIUS !!

Thank you. Just saved hours of manual adjustments !!

Whilst you are on the ball, there will be some cells that have the "T" in twice. I was thinking that i could alter the formula to just look for N,F & R, and have a default value return if it finds nothing. This would solve the "T" problem, as there would never be a combo.

Is that possible?

Thanks again.

 

[Participant]

Is the string before and after always the same, i.e. 3SV19 and 022M? If so I'd probably use substitute formulae and a lookup table to isolate the letters:

Seems quite complicated but that was my first reaction.

 

[fullmetaltony]

Indeed, just remove the "T" bit and replace the "" at the end with the default value for false.
like thus

=IF(ISERROR(FIND("F",A5))=FALSE,"FL",IF(ISERROR(FIND("R",A5))=FALSE,"SPLIT",IF(ISERROR(FIND("N",A5))=FALSE,"316","OV")))

Though like before, remove the random-ish spaces lol

 

[fullmetaltony]

Is the string before and after always the same, i.e. 3SV19 and 022M? If so I'd probably use substitute formulae and a lookup table to isolate the letters:

Seems quite complicated but that was my first reaction.

That would work, but it's always much nicer to have a formula you can just cut paste against the cell you're interogating, I find.

Not seen the SUBSTITUTE function used before, thanks for that, I may have use for it in the future.

 

[Countcussy]

The string is actually a pump manufacturers stock code. Each letter/number corresponds to an option. Generally speaking, there are 4 versions of each pump. All identical in terms of performance, but with differing port & material confugurations. Single and three phase versions also. I can decipher the number no problem, but we have some new staff starting, and I wanted to make it a little easier to understand, and import back into Sage

So now

3SV19T022M = 3SV19T022M - 240v - Oval Flange
3SV19F022M = 3SV19F022M - 240v - Round Flange
3SV19R022M = 3SV19R022M - 240v - Round/Offset Ports
3SV19N022M = 3SV19N022M - 240v - Round/316 SS

There are approx 400 pumps in the range, each with varying options. The amount of time this has saved me does not bear thinking about. I was only trying to help the new guys !!!!

Formula in place and working perfectly.

Thank you.

 

[fullmetaltony]

That's what I love about excel, I don't "go through" anything now. Unless it absolutely cannot be done in excel.
Not that I like to tell people around here I've done it in a few minutes in excel
"Yeah, took all morning to go through and make all those changes"