More Excel Help

  steviegee 13:37 17 Sep 2007
Locked

Along the lines of this thread click here I need another formula.

If it is AAROC18 I need a formula to make it AAROC_18A or AAROC18D to read AAROC18D.

And if it is AU8018 to read AU80__18A or AU8018D to read AU80__18D and so on.

There will always be different letters and numbers but either 6 or 7 characters.

The info starts in A2 and the result will be in E2 downwards.

  steviegee 13:38 17 Sep 2007

Sorry AAROC18D needs to read AAROC_18D!

  VoG II 13:51 17 Sep 2007

AAROC18D is 8 characters.

  steviegee 14:00 17 Sep 2007

Ok my mistake! But thats how it may be.

  VoG II 14:09 17 Sep 2007

Try in E2 and copied down

=IF(LEN(A2)=8,LEFT(A2,5)&"_"&RIGHT(A2,3),IF(LEN(A2)=6,LEFT(A2,4)&"__"&RIGHT(A2,2)&"A",IF(ISNUMBER(VALUE(RIGHT(A2,1))),LEFT(A2,5)&"_"&RIGHT(A2,2)&"A",LEFT(A2,4)&"__"&RIGHT(A2,3))))

  VoG II 14:10 17 Sep 2007

=IF(LEN(A2)=8,LEFT(A2,5)&"_"&RIGHT(A2,3), IF(LEN(A2)=6,LEFT(A2,4)&"__"&RIGHT(A2,2)&"A", IF(ISNUMBER(VALUE(RIGHT(A2,1))),LEFT(A2,5)&"_"&RIGHT(A2,2)&"A", LEFT(A2,4)&"__"&RIGHT(A2,3))))

  steviegee 14:49 17 Sep 2007

Thanks. Worked with 98% of them and rest I did manually. Thanks again VoG™.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?