Making AES31/12/200KG into 200
Posted by Tim on June 21, 2001 8:25 AM
Does any1 know how i can make a collumn with codes in like this into just the number after the last delimiter.
e.g.
AES31/12/200KG into 200
SP27/12/120KG into 120
The first part b4 the 1st "/" varies in size. but the rest of the code will always be the same size.... with bit between the 1st & 2nd "/" always being 2 characters long and the bit after the last delimiter will always be five characters long.
Cheers
Tim
Posted by Aladin Akyurek on June 21, 2001 8:50 AM
Try:
=LEFT(RIGHT(A1,5),3)
Aladin
Posted by Aladin Akyurek on June 21, 2001 9:38 AM
Moreover...
If you want the result as number-formatted, use the following formula instead:
=LEFT(RIGHT(A1,5),3)+0
============ Try: =LEFT(RIGHT(A1,5),3) Aladin
Posted by tim on June 21, 2001 12:54 PM
Re: Moreover...
It works on all the code apart from the ones with out KG on the end
e.g. AES31/12/200 If you want the result as number-formatted, use the following formula instead: =LEFT(RIGHT(A1,5),3)+0 ============ : Try
Posted by Aladin Akyurek on June 21, 2001 1:11 PM
Re: Moreover...
Lets get them too... ;)
with
=IF(RIGHT(A5,2)="KG",LEFT(RIGHT(A5,5),3)+0,RIGHT(A5,3)+0)
Aladin It works on all the code apart from the ones with out KG on the end AES31/12/200 : If you want the result as number-formatted, use the following formula instead
Posted by Tim on June 22, 2001 2:09 AM
Nice 1 --- it worked...
Posted by Tim on June 22, 2001 2:53 AM
Now i need to get the 12 out of AES31/12/200KG
Now i need to get the 12
AES31/12/200KG
Posted by Aladin Akyurek on June 22, 2001 3:24 AM
Re: Now i need to get the 12 out of AES31/12/200KG
AES31/12/200KG
Tim
You mean you want to extract 12 into a different cell? More precisely, anything between last 2 slashes into a different cell?
Aladin
====================
Posted by Tim on June 22, 2001 3:57 AM
yeah, - is it possible...?
Posted by Aladin Akyurek on June 22, 2001 7:31 AM
Re: yeah, - is it possible...?
Tim
To get those 12's,
in C1 enter: =IF(RIGHT(A1,2)="KG",LEFT(RIGHT(A1,8),2)+0,LEFT(RIGHT(A1,6),2)+0)
where I assume A1 contains the first string to be decomposed.
Aladin
========
