# Excel Question

steviegee 13:07 09 Jul 07
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 07

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

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 07

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

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 07

Thanks VoG you are a genius!!

steviegee 13:56 09 Jul 07

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 07

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

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation techniques

How to mine Bitcoin on Mac