Difference between revisions of "MSexcel"
Jump to navigation
Jump to search
| Line 7: | Line 7: | ||
;<nowiki>=IF(LEN(VLOOKUP(D2,A2:B10,2,0))=0,"",VLOOKUP(D2,A2:B10,2,0))</nowiki> | ;<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. | :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='VBScript'> | ||
| + | Sub EmptyCells() | ||
| + | ' | ||
| + | ' EmptyCells Macro | ||
| + | ' | ||
| + | ' | ||
| + | For Each cell In Range("C2:D10) | ||
| + | If (cell.Value = "") Then cell.ClearContents | ||
| + | Next | ||
| + | End Sub | ||
| + | </syntaxhighlight> | ||
Revision as of 11:49, 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