converting a 6 digit string into a 3 character string

Dorse411

New Member
Joined
Aug 16, 2019
Messages
3
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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))
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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).
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top