More Excel Help

steviegee 13:37 17 Sep 07
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 07

Sorry AAROC18D needs to read AAROC_18D!

VoG II 13:51 17 Sep 07

AAROC18D is 8 characters.

steviegee 14:00 17 Sep 07

Ok my mistake! But thats how it may be.

VoG II 14:09 17 Sep 07

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 07

=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 07

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.

Intel Coffee Lake 8th-gen Core processors release date rumours

1995-2015: How technology has changed the world in 20 years

Framestore’s haunting post-WWII title sequence for new BBC series SS-GB

Best iPhone games 2017 | Best iPad games 2017: 162 fantastic iOS games that you need to play right…