# More Excel Help

steviegee 13:37 17 Sep 2007
Locked

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

Honor 9 Lite review

How Social Media has Propelled Political Graphic Design and Art in the Last Decade

The best kids apps for iPhone & iPad 2018

HomePod d’Apple : date de sortie, prix et fiche technique