Excel Question

  steviegee 13:07 09 Jul 07

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


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


  VoG II 13:31 09 Jul 07

Sorry, should be in M1 enter:


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


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

AR15620 001


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.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

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

This abstract video touches on division in our technologic world

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…