converting a 6 digit string into a 3 character string

[Dorse411]

First post.

Need to get LUN ID from a Disk UID

initial DISK UID is a string of around 20 odd characters. I only need the last 6 which i can do.

but then i need to convert that 6 digit string into a 3 character string. like so

initial 6 digit string
3 4 4 5 3 4

only intrested in characters 2 4 and 6, now if the number proceeding the character i want is a 3 then the character i want is a number, if the proceeding number is a 4 then it is alphabetical.

so 3 4 4 5 3 4 becomes - 4E4

how would i fabricate a formula to sort this out for me?

any help appreciated.

Dorse411

 

[steve the fish]

Re: converting a 6 digit string into a 3 character string(HELP)

Hi. Test this:

=CHAR(MID(A1,LEN(A1)-4,1)+IF(MID(A1,LEN(A1)-5,1)="3",48,64))&CHAR(MID(A1,LEN(A1)-2,1)+IF(MID(A1,LEN(A1)-3,1)="3",48,64))&CHAR(MID(A1,LEN(A1),1)+IF(MID(A1,LEN(A1)-1,1)="3",48,64))

 

[Peter_SSs]

Re: converting a 6 digit string into a 3 character string(HELP)

Welcome to the MrExcel board!

Can characters 1, 3 & 5 of those last 6 characters be anything else other than 3 or 4? If so, what should happen? Examples please.

 

[Dorse411]

Re: converting a 6 digit string into a 3 character string(HELP)

Cheers for the help chaps.

Steve the fish. that worked excellently, have tested and proved. thanks a lot.

Peter_SSs havent come across one yet where its not 3 or 4.

 

[XOR LX]

Re: converting a 6 digit string into a 3 character string(HELP)

Alternative, for Office 365, array formula**:

=CONCAT(CHAR(MID(A1,{2,4,6},1)+IF(MID(A1,{1,3,5},1)="3",48,64)))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).