Excel Numbering - (O365)

[dgoodrid]

Hi, I am trying to create a report that shows if a the machines listed are at the latest BIOS versions from data from another report, I have mocked up a sheet to show that the info would be like and the strugles i have with some of the data as its reporting single numbers as 10's not Units (ie 9=90 not 09) i have even tried ti split all this and just extract the left and right to then add a 0 to the single number but obvs when you say take first or last of a cell with 4 in both will come back with 4 - so i guess whats the easiest way to convert 1.8.0 or 1.7.0 example to 1.08.0 or 1.07.0 but leave the ones as 1.32.0 alone

 

[dgoodrid]

Update: I would manually just do it but there are over 1500 devices and i dont want to check each one individually lol - also the data is updated every 2 weeks so the main data (Model and Current BIOS) would be replaced and maybe the latest if there is any

 

[felixstraube]

How about?:

Libro1
	D	E
5	1.32.0	1.32.0
6	1.8.0	1.08.0
7	1.7.1	1.07.1
8	1.31.2	1.31.2
Hoja1

Cell Formulas
Range	Formula
E5:E8	E5	=LET(t,TEXTSPLIT(D5,"."),CHOOSECOLS(t,1)&"."&TEXT(CHOOSECOLS(t,2),"00")&"."&CHOOSECOLS(t,3))

 

[Rick Rothstein]

Using the setup that @felixstraube posted, this formula would also work...

=TEXTJOIN(".",,TEXT(TEXTSPLIT(D5,"."),{"0","00","0"}))

 

[felixstraube]

There had to be a more elegant formula. Nice one @Rick Rothstein

 

[jgarland]

See if the attached file will work for you.

BIOS Conversions.xlsx

Shared with Dropbox

www.dropbox.com

 

[Fluff]

@jgarland please post your suggestion to the board as per Rule#4 thanks.

 

[jgarland]

My apologies, I was not aware I could not post links. I do believe my solution will work for the OP, how do I get that info to him?

 

[Fluff]

You can either use the XL2BB add-in (see my signature for a link) or you can post the formula (using the "xls" code tag in the reply window) along with an explanation.

 

[Phuoc]

Or try

Book1
	D	E
5	1.32.0	1.32.0
6	1.8.0	1.08.0
7	1.7.1	1.07.1
8	1.31.2	1.31.2
Sheet1

Cell Formulas
Range	Formula
E5:E8	E5	=IF(LEN(D5)=6,D5,REPLACE(D5,3,0,0))