# Excel Help Again

steviegee 15:20 03 Oct 07
Locked

Sorry to be so dim but as regards to these click here I am getting a bit confused!!! I now have starting in cell B2 AAROC18 001 which needs to be in cell L2 AAROC_18A and if a cell reads AAROC20D 001 it needs to read in cell L2 AAROC_20D and so on. Some may be AUA214 001 which need to be AUA2__14A or AUA214D 001 which needs to be AUA2__14D. Is VoG about!!

harps1h 16:27 03 Oct 07

VoG II 18:56 03 Oct 07

=IF(LEN(LEFT(B2, LEN(B2)-4))=8, LEFT(LEFT(B2,LEN(B2)-4), 5) & "_" & RIGHT(LEFT(B2, LEN(B2)-4), 3), IF(LEN(LEFT(B2,LEN(B2)-4))=6, LEFT(LEFT(B2,LEN(B2)-4),4) & "__" & RIGHT(LEFT(B2,LEN(B2)-4),2) & "A", IF(ISNUMBER(VALUE(RIGHT(LEFT(B2,LEN(B2)-4),1))), LEFT(LEFT(B2,LEN(B2)-4),5) & "_" & RIGHT(LEFT(B2,LEN(B2)-4),2) & "A", LEFT(LEFT(B2,LEN(B2)-4),4) & "__" & RIGHT(LEFT(B2,LEN(B2)-4),3))))

steviegee 07:43 04 Oct 07

Thats excellant VoG. Is it also possible to incorparate into this formula if it is the following format? (FTGRAP14 001 to FTGRAP14A) and (FTGRAP18D to FTGRAP18D) and also (AR618 001 to AR6___18A) and (AR618D 001 to AR6___18D)or does that get to complicated!! Then I will have all possibilities covered. Thanks again.

steviegee 07:44 04 Oct 07

Sorry the (FTGRAP18D to FTGRAP18D had to be FTGRAP18D 001 to FTGRAP18D)

steviegee 12:12 04 Oct 07

^

VoG II 16:57 04 Oct 07

Too complicated for me - I'm not much good with formulas.