Excel Question

  steviegee 13:07 09 Jul 2007
Locked

I have a column with numbers like below and I need to know a formula that may help me create a new column with a new number.

AR15620 001 - Needs to be AR156_20A_001
AR15620D 001 - Needs to be AR156_20D_001
VLAST14 001 - Needs to be VLAST_14A_001
and so on...

Where there is no letter it needs an 'A' (AR15620'A').

Is this possible? (I do need to split it up with underscores too).

  VoG II 13:18 09 Jul 2007

=IF(LEN(A1)=12,LEFT(A1,5)&"_"&MID(A1,6,3)&"_"&RIGHT(A1,3),LEFT(A1,5)&"_"&MID(A1,6,2)&"A_"&RIGHT(A1,3))

and copied down.

  steviegee 13:28 09 Jul 2007

Sorry to be awkward but the original numbers are in column C and the new numbers need to be in column M.

  VoG II 13:29 09 Jul 2007

=IF(LEN(M1)=12,LEFT(M1,5)&"_"&MID(M1,6,3)&"_"&RIGHT(M1,3),LEFT(M1,5)&"_"&MID(M1,6,2)&"A_"&RIGHT(M1,3))

  VoG II 13:31 09 Jul 2007

Sorry, should be in M1 enter:

=IF(LEN(C1)=12,LEFT(C1,5)&"_"&MID(C1,6,3)&"_"&RIGHT(C1,3),LEFT(C1,5)&"_"&MID(C1,6,2)&"A_"&RIGHT(C1,3))

and copy down.

  steviegee 13:44 09 Jul 2007

Thanks VoG you are a genius!!

  steviegee 13:56 09 Jul 2007

If you are there again Vog how about when they are like this:-

AU8013 001 - AU80__13A_001
AUA419D 001 - AUA4__19D 001

i.e One less number at the beginning.

  VoG II 14:06 09 Jul 2007

=IF(LEN(C1)=11,LEFT(C1,4)&"__"&MID(C1,5,3)&"_"&RIGHT(C1,3),LEFT(C1,4)&"__"&MID(C1,5,2)&"A_"&RIGHT(C1,3))

but note that there isn't an easy formula to combine the two formulas because

AR15620 001

and

AUA419D 001

both have the same number of characters (11) but are in different formats.

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

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

Halloween Photoshop & Illustrator tutorials: 20 step-by-step guides to creating spook-tacular…

iPhone X news: Release date, price, new features & specs

Comment créer, modifier et réinitialiser un compte Apple ?