Text & Numbers in the same cell

[mdwnn7]

Hi

I have a spread sheet that calculated pick rates and down time (non picking activity's)
The assembly number use to be a 7 digit number (see sample spread sheet attached)
Due to a system change this number now has a prefix (EU,CC,ND or STD)
To calculate down time, I used a formula to identify text. then calculate the time to the next pick, this is then taken off the work time at the end of the day
since the system change the assembly number ( with prefix) is text
I have tried the formula
=IF(LEFT(G3,2)="CC","",T(G3)) that appeared to work, however when i expanded it to =IF(LEFT(G3,2)=OR("CC","EU","ST","ND"),"",T(G3)) i get a Value error

any suggestions to how i can identify/ remove the prefix?

Mick

[Glenn Kennedy]

I'm not sure if I really understand what you want... but b any chance does this do it???

=IF(--TRIM(LOOKUP(99^99,--("0"&MID(G2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},G2&"0123456789")),ROW(1:10000)))))=0,G2,--TRIM(LOOKUP(99^99,--("0"&MID(G2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},G2&"0123456789")),ROW(1:10000))))))

[JohnTopley]

Try ..

=IF(OR(LEFT(G3,2)="CC",LEFT(G3,2)="EU",LEFT(G3,2)="ST",LEFT(G3,2)="ND"),"",T(G3))

[mdwnn7]

Hi
Thanks for quick reply's
Glenn I was after something that separated the Assembly number from all other data (or i think that's what I was after)

John, yes thanks that will work (I was almost there, I need to try harder next time)

Mick