2090
Comment: Add easy formula.
|
2153
Update excel to moinmoin to detect header in first column.
|
Deletions are marked like this. | Additions are marked like this. |
Line 63: | Line 63: |
=CONCATENATE("|| ",B2&REPT(" ",MAX($B$1-LEN(B2),0))," || ",C2&REPT(" ",MAX($C$1-LEN(C1),0))," || ",D2&REPT(" ",MAX($D$1-LEN(D1),0))," || ",E2&REPT(" ",MAX($E$1-LEN(E1),0) )," || ",F2&REPT(" ",MAX($F$1-LEN(F1),0))," ||") | =IF(LEN(C2&D2&E2&F2)>0,CONCATENATE("|| ",B2&REPT(" ",MAX($B$1-LEN(B2),0))," || ",C2&REPT(" ",MAX($C$1-LEN(C1),0))," || ",D2&REPT(" ",MAX($D$1-LEN(D1),0))," || ",E2&REPT(" ",MAX($E$1-LEN(E1),0) )," || ",F2&REPT(" ",MAX($F$1-LEN(F1),0))," ||"),CONCATENATE("||<-5> ''' ",B2," ''' ||")) |
OpenOffice Basic snipits
Mask IP
Public Function IP(inIp As Variant, Optional mask as byte) as String Dim a(5) As Long Dim b(5) As Long Dim s() As String If Not IsNumeric(inIP) Then s = Split(inIp,".") dim v as Currency v = ( Cbyte(s(0))*2^24 + Cbyte(s(1))*2^16 + Cbyte(s(2))*2^8 + Cbyte(s(3)) ) else v = 0.0 + inIP rem and ( 2^mask ) End If dim bin(31) as Byte for i = 31 to 0 Step -1 If v >= 2^i then If IsMissing(mask) Then bin(i)= 1 Else If i >= (32-mask) Then bin(i) = 1 Else bin(i) = 0 End If End If v = v - 2^i Else bin(i) = 0 End If Next i for i = 0 to 7 a(1) = a(1) + 2^i*bin(i+24) a(2) = a(2) + 2^i*bin(i+16) a(3) = a(3) + 2^i*bin(i+8) a(4) = a(4) + 2^i*bin(i) next i dim t(4) as String t(1) = Right("000" & CStr(a(1)),3) t(2) = Right("000" & CStr(a(2)),3) t(3) = Right("000" & CStr(a(3)),3) t(4) = Right("000" & CStr(a(4)),3) If IsMissing(mask) Then IP = t(1)&"."&t(2)&"."&t(3)&"."&t(4) Else t(0) = CStr(mask) IP = t(1)&"."&t(2)&"."&t(3)&"."&t(4)&"/"&t(0) End If End Function
- concatenate=con
* 1st Column, top row max len/space add. and max len with =MAX(LEN(C2:C2000)) + Ctrl + Shift + Enter =IF(LEN(C2&D2&E2&F2)>0,CONCATENATE("|| ",B2&REPT(" ",MAX($B$1-LEN(B2),0))," || ",C2&REPT(" ",MAX($C$1-LEN(C1),0))," || ",D2&REPT(" ",MAX($D$1-LEN(D1),0))," || ",E2&REPT(" ",MAX($E$1-LEN(E1),0) )," || ",F2&REPT(" ",MAX($F$1-LEN(F1),0))," ||"),CONCATENATE("||<-5> ''' ",B2," ''' ||"))