Shortening This Formula

Hi, I'm having some problems with this formula due to its length at the moment I've got P3 as the target however it should be Datasheet!B6 but doing this throws it way over the limit so I wondered if anyone could help me shorten it?

Basically the formula is taking the 2nd octet from a IP address and adding leading zeros if its only 1 or 2 characters long.

IF(LEN((MID(MID(MID(SUBSTITUTE(P3,".","^",1),1,256),FIND("^",SUBSTITUTE(P3,".","^",1)),256),2,FIND(".",MID(MID(SUBSTITUTE(P3,".","^",1),1,256),FIND("^",SUBSTITUTE(P3,".","^",1)),256))-2)))=1,"00"& (MID(MID(MID(SUBSTITUTE(P3,".","^",1),1,256),FIND("^",SUBSTITUTE(P3,".","^",1)),256),2,FIND(".",MID(MID(SUBSTITUTE(P3,".","^",1),1,256),FIND("^",SUBSTITUTE(P3,".","^",1)),256))-2)),IF(LEN((MID(MID(MID(SUBSTITUTE(P3,".","^",1),1,256),FIND("^",SUBSTITUTE(P3,".","^",1)),256),2,FIND(".",MID(MID(SUBSTITUTE(P3,".","^",1),1,256),FIND("^",SUBSTITUTE(P3,".","^",1)),256))-2)))=2,"0"& (MID(MID(MID(SUBSTITUTE(P3,".","^",1),1,256),FIND("^",SUBSTITUTE(P3,".","^",1)),256),2,FIND(".",MID(MID(SUBSTITUTE(P3,".","^",1),1,256),FIND("^",SUBSTITUTE(P3,".","^",1)),256))-2)),(MID(MID(MID(SUBSTITUTE(P3,".","^",1),1,256),FIND("^",SUBSTITUTE(P3,".","^",1)),256),2,FIND(".",MID(MID(SUBSTITUTE(P3,".","^",1),1,256),FIND("^",SUBSTITUTE(P3,".","^",1)),256))-2))))

Re: Shortening This Formula

Quote from filo65

truCido,

welcome to ozgrid. your formula appears to be a little bit "heavy". Would eventually =DEC2BIN(.. , 8 ) help you?

filippo

Thanks for the welcome Um possibly but looking at the formula Parsnip just posted dont think I'll need to. Thanks anyway

Quote from Parsnip

Hi

The following will convert all octets of the IP address to have leading zeroes if required (this assumes that in A1 you have an IP address in the format 123.46.89.123 - ie no alpha characters):

=TEXT(--MID(SUBSTITUTE(A1,".",REPT(" ",100)),1,100),"000")&"."&TEXT(--MID(SUBSTITUTE(A1,".",REPT(" ",100)),100,100),"000")&"."&TEXT(--MID(SUBSTITUTE(A1,".",REPT(" ",100)),200,100),"000")&"."&TEXT(--MID(SUBSTITUTE(A1,".",REPT(" ",100)),300,100),"000")

If you have the Morefunc add-in installed then you can make use of one of its formulas to shorten further:

=MCONCAT(TEXT(--MID(SUBSTITUTE(A1,".",REPT(" ",100)),{1,100,200,300},100),"000"),".")

Hope this helps!

Richard

Display More

Wow thanks Richard thats awesome!!! had to change it a bit as the spreadsheet only has the first 3 octets (long story lol). Thanks a lot