MSexcel: Difference between revisions
Jump to navigation
Jump to search
Created page with "Some formula's to remember if you have to use Excel ;TEKST(A1,"00000") :Fill out the value of cell A1 with 0's on the left side. :TEKST(2,"0000") => 0002" |
No edit summary |
||
| (3 intermediate revisions by the same user not shown) | |||
| Line 4: | Line 4: | ||
:Fill out the value of cell A1 with 0's on the left side. | :Fill out the value of cell A1 with 0's on the left side. | ||
:TEKST(2,"0000") => 0002 | :TEKST(2,"0000") => 0002 | ||
;<nowiki>=IF(LEN(VLOOKUP(D2,A2:B10,2,0))=0,"",VLOOKUP(D2,A2:B10,2,0))</nowiki> | |||
:VLOOKUP returns 0 values for empty cells by default. This looks better. | |||
==Clear cells with empty string== | |||
The VLOOKUP formula above sets an empty string if there is no value in the lookup-range (A2:B10). To make these cells really empty create and execute a macro like: | |||
<syntaxhighlight lang=vbnet> | |||
Sub EmptyCells() | |||
' | |||
' EmptyCells Macro | |||
' | |||
' | |||
For Each cell In Range("C2:D10") | |||
If (cell.Value = "") Then cell.ClearContents | |||
Next | |||
End Sub | |||
</syntaxhighlight> | |||
Latest revision as of 09:56, 6 May 2020
Some formula's to remember if you have to use Excel
- TEKST(A1,"00000")
- Fill out the value of cell A1 with 0's on the left side.
- TEKST(2,"0000") => 0002
- =IF(LEN(VLOOKUP(D2,A2:B10,2,0))=0,"",VLOOKUP(D2,A2:B10,2,0))
- VLOOKUP returns 0 values for empty cells by default. This looks better.
Clear cells with empty string
The VLOOKUP formula above sets an empty string if there is no value in the lookup-range (A2:B10). To make these cells really empty create and execute a macro like:
Sub EmptyCells()
'
' EmptyCells Macro
'
'
For Each cell In Range("C2:D10")
If (cell.Value = "") Then cell.ClearContents
Next
End Sub