Text Length Conversion

[Tikey]

I am currently using several stages to achieve my goal andwonder whether there is a more efficient way to do this, hopefully just in onecell.

I have a cell which contains letters and numbers which canbe between 6 and 12 characters long and for cells which contain more than 6, Iwish to be able to select certain characters. My current method is:

In cell A1 - SE1553346646
In cell B1 – I then convert to show many charters A1contains with =LEN(A1), in this case 12
In cell C1 – I always want the first 4 characters so I amusing =MID(A1,1,4), giving SE15
In cell D1 – As the other characters could be at various positionsdepending on how many characters are in A1 I am using a nested IF statement , =IF(B1=6,MID(A1,5,2),IF(B1=8,MID(A1,6,2),IF(B1=10,MID(A1,7,2),IF(B1=12,MID(A1,8,2))))),in this case giving 46
In cell E1 – I then use =C1&D1 to provide my final answerof SE1546

Is there a better more efficient way to do this, can it beachieved within one cell ?

Thanks

 

[Fluff]

How about
=LEFT(A6,4) & MID(A6,CHOOSE(LEN(A6),,,,,,5,,6,,7,,8),2)

 

[Tikey]

How about
=LEFT(A6,4) & MID(A6,CHOOSE(LEN(A6),,,,,,5,,6,,7,,8),2)

Works a treat.

Thanks very much.

 

[Fluff]

Glad to help & thanks for the feedback.