Uitgewerkte voorbeelden met Excel Formules
1. Jaar-weeknummering als 1 getal. Hierbij moeten de eerste 9 weken wel 2 cijfers hebben.
Oplossing:
=TEKST.SAMENVOEGEN(JAAR([Datum]);TEKST(WEEKNUMMER([Datum]);"00"))
Het weeknummer wordt als tekst weergegeven. Door "00" wordt de tekst met voorloopnullen aangevuld (in dit geval tot 2 karakters.
Andere voorbeelden van: https://support.office.com/en-us/article/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c
=TEXT(1234.567,"$#,##0.00") | Currency with a thousands separator and 2 decimals, like $1,234.57. Note that Excel rounds the value to 2 decimal places. |
=TEXT(TODAY(),"MM/DD/YY") | Today’s date in MM/DD/YY format, like 03/14/12 |
=TEXT(TODAY(),"DDDD") | Today’s day of the week, like Monday |
=TEXT(NOW(),"H:MM AM/PM") | Current time, like 1:29 PM |
=TEXT(0.285,"0.0%") | Percentage, like 28.5% |
=TEXT(4.34 ,"# ?/?") | Fraction, like 4 1/3 |
=TRIM(TEXT(0.34,"# ?/?")) | Fraction, like 1/3. Note this uses the TRIM function to remove the leading space with a decimal value. |
=TEXT(12200000,"0.00E+00") | Scientific notation, like 1.22E+07 |
=TEXT(1234567898,"[<=9999999]###-####;(###) ###-####") | Special (Phone number), like (123) 456-7898 |
=TEXT(1234,"0000000") | Add leading zeros (0), like 0001234 |
=TEXT(123456,"##0° 00' 00''") | Custom - Latitude/Longitude |