MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

========