All versionsExamples need Excel 2021 or laterText

UPPER

HOOFDLETTERS in Dutch Excel

Converts every letter in a text to uppercase and leaves digits, spaces and punctuation as they are.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABCD
1Journal as typedEntry no.AmountJournal
2vrk25-0412€12,100.00€ 12.100,00VRK=UPPER(A2)=UPPER(A2)
3Ink25-0413€3,025.00€ 3.025,00
4BNK25-0414€8,470.00€ 8.470,00
5mem25-0415€1,500.00€ 1.500,00
6Vrk25-0416€6,050.00€ 6.050,00

Result: VRK

=UPPER(A2)
=UPPER(A2)

What UPPER does and when to use it

What it does: it turns every letter of a text into a capital, for example ink-25 into INK-25. Digits, spaces and hyphens stay as they are.

When to use it: for codes that people type by hand, such as product codes or batch numbers, when the text goes to a case-sensitive system, is compared with EXACT or searched with FIND, or a list has to look uniform. Lookups and COUNTIF ignore case, so a lookup rarely needs it. For names and titles, PROPER is the better tool.

How Excel evaluates it

Excel reads the argument. If it is a cell reference, it takes the cell's value; if the cell is empty, the result is an empty text.

It then walks through the text character by character: each lowercase letter is replaced by its uppercase form, including accented letters such as é, and every other character is copied as it is. The result is always text, even when the input was a number, and it is a new value: the original cell is not changed.

Watch out: Excel's = and <> ignore case, so =UPPER(A2)=UPPER(B2) and A2<>UPPER(A2) can never reveal a difference in capitals; use EXACT for that.

Mental model

It is the Caps Lock key applied to a cell: only letters react to it.

Terms used on this page: reference, argument, cell

Syntax

Official UPPER documentation
UPPER(text)
UPPER(text)
ArgumentTypeMeaning
textRequiredThe text to convert; a cell reference or text in quotes.

Return value:Text: the text in uppercase

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

Make journal codes uniform

In the XAF auditfile of Veldhuis Installatie BV, manual entries carry journal codes typed by different people: vrk, Ink, BNK, mem. You want to count and total the entries per journal in a pivot table, and the pivot should show one group for VRK, not several spellings. You add a column that shows every journal code in capitals.

ABCDE
1Journal as typedEntry no.AmountJournal
2vrk25-0412€12,100.00€ 12.100,00VRK=UPPER(A2)=UPPER(A2)
3Ink25-0413€3,025.00€ 3.025,00
4BNK25-0414€8,470.00€ 8.470,00
5mem25-0415€1,500.00€ 1.500,00
6Vrk25-0416€6,050.00€ 6.050,00
7
Formula cell (D2)Input

Formula in D2

=UPPER(A2)
=UPPER(A2)

Result: VRK

Step by step
  1. Excel reads A2 and finds the text vrk, three characters long.
  2. It goes through the characters one by one: v becomes V, r becomes R, k becomes K. All three are letters, so all three change.
  3. The result in D2 is VRK. A2 still holds vrk; UPPER made a new value and did not edit the source, so the export stays as you received it.
  4. Copy D2 down to D6: Ink becomes INK, BNK stays BNK, mem becomes MEM and Vrk becomes VRK. Rows 2 and 6 now show the same code, so a pivot table on column D groups them: VRK holds 12,10012.100 + 6,0506.050 = 18,15018.150.

Watch out: If A2 is empty, D2 shows an empty text, not 0. That is fine for display, but COUNTA counts the cell as filled, so a count of journal lines can come out too high.

More on this example: why it works, 1 variation

Why it works

UPPER only touches letters, so a code keeps its shape and the digits and hyphens around it stay as they were. Because the result is a new text value, you keep the typed version as evidence of what the client delivered and use the clean column for your analysis.

Variations

=UPPER(A2:A6)
=UPPER(A2:A6)

In Excel 2021 or Microsoft 365 one formula in D2 spills all five codes at once; there is nothing to copy down.

Intermediate

Clean relation codes before matching to the subledger

You reconcile the open items of the receivables and payables aging list to the general ledger. The client typed the relation codes by hand: d1001, C2001, and some with a stray space at the start or the end. The subledger lists D1001 and C2001 in capitals without spaces, and your case-sensitive matching tool rejects every row that differs.

You want a column with codes that match, without retyping anything.

ABCDE
1Relation as typedInvoice no.Open amountRelation
2d1001F2025-0871€4,235.00€ 4.235,00D1001=UPPER(TRIM(A2))=UPPER(TRIM(A2))
3C2001F2025-0412€1,815.00€ 1.815,00
4d1003F2025-0905€9,680.00€ 9.680,00
5c2004F2025-0377€605.00€ 605,00
6D1001F2025-0918€2,420.00€ 2.420,00
7
Formula cell (D2)InputMarks: extra spaceNote: A2 starts with a space and A4 and A6 end with one; all three look normal on screen.

Formula in D2

=UPPER(TRIM(A2))
=UPPER(TRIM(A2))

Result: D1001

Step by step
  1. Excel evaluates the inner function first. TRIM reads A2, which holds a space followed by d1001, six characters, and removes the leading space: d1001.
  2. UPPER receives d1001 and converts the only letter: D1001.
  3. D2 shows D1001, five characters, the same as the code in the subledger.
  4. Without TRIM the formula would return a space followed by D1001, six characters. It looks right in the cell and the matching tool still rejects it, which is why the space has to go first.
  5. Copy D2 down: C2001 stays C2001, d1003 with its trailing space becomes D1003, c2004 becomes C2004, and D1001 with a trailing space becomes D1001. Row 2 and row 6 now carry the same relation code, so you can add up the open items per relation.

Watch out: A non-breaking space copied from a PDF or a web portal survives TRIM. If a cleaned code still has six characters, check with LEN and read the TRIM page for the fix.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

Two problems, two functions, applied inside out: TRIM deals with the spaces, UPPER with the case. This is the situation where case actually matters: not for Excel, which would match d1001 and D1001 happily, but for the tool or system that receives your file.

More to watch out for

  • UPPER does not validate: a typo like D1O01 (letter O instead of zero) stays D1O01 and matches no relation. Compare the result against the relation list with COUNTIF and follow up the codes that return 0.

Variations

=TRIM(UPPER(A2))
=TRIM(UPPER(A2))

The order does not matter here, because neither function touches what the other changes. Cleaning first is the convention, so the next reader sees the cleaning step first.

Advanced

Count the invoice numbers not yet in capitals

You match the invoices of the payables subledger to the general ledger in a tool that treats f2025-0872 and F2025-0872 as two different invoices, so they would show up as differences.

Before you load the list you want one number: how many invoice numbers are not fully in capitals yet. The obvious test, A2<>UPPER(A2), returns FALSE for every row, because <> ignores case; EXACT does not.

ABCDE
1Invoice no.AmountRows to fix
2F2025-0871€4,235.00€ 4.235,0033=SUMPRODUCT(1 - EXACT(A2:A7, UPPER(A2:A7)))=SUMPRODUCT(1 - EXACT(A2:A7; UPPER(A2:A7)))
3f2025-0872€1,815.00€ 1.815,00
4F2025-0873a€9,680.00€ 9.680,00
5F2025-0874€605.00€ 605,00
6F2025-0875€2,420.00€ 2.420,00
7f2025-0876€7,260.00€ 7.260,00
8
Formula cell (D2)Input

Formula in D2

=SUMPRODUCT(1 - EXACT(A2:A7, UPPER(A2:A7)))
=SUMPRODUCT(1 - EXACT(A2:A7; UPPER(A2:A7)))

Result: 33

Step by step
  1. Excel starts with the innermost function. UPPER(A2:A7) produces a six-item array: F2025-0871, F2025-0872, F2025-0873A, F2025-0874, F2025-0875, F2025-0876.
  2. EXACT compares each original with its uppercase version, case-sensitively: F2025-0871 equals F2025-0871, TRUE; f2025-0872 against F2025-0872, FALSE; F2025-0873a against F2025-0873A, FALSE; then TRUE, TRUE, FALSE. The array is {TRUE;FALSE;FALSE;TRUE;TRUE;FALSE}.
  3. 1 - TRUE is 0 and 1 - FALSE is 1, so the subtraction turns the array into {0;1;1;0;0;1}: a 1 for every row that needs fixing.
  4. SUMPRODUCT adds the array: 0 + 1 + 1 + 0 + 0 + 1 = 3. D2 shows 3.
  5. Fix A3 to F2025-0872 and the count drops to 2 without touching the formula.

Watch out: =SUMPRODUCT(--(A2:A7<>UPPER(A2:A7))) looks equivalent and silently returns 0 for any data, because <> compares without regard to case. There is no error to warn you, and you would report a clean list that is not clean.

More on this example: why it works, 1 more pitfall, 2 variations

Why it works

EXACT is one of the few places in Excel where case counts, so comparing a value with its own uppercase version tells you whether it was already in capitals. The subtraction from 1 does two jobs at once: it flips the logic (1 means wrong) and converts TRUE/FALSE into numbers that SUMPRODUCT can add.

More to watch out for

  • A row with a trailing space counts as correct: EXACT compares F2025-0871 followed by a space with its uppercase version, which has the same space. Combine with TRIM if spaces are also a problem.

Variations

=IF(EXACT(A2, UPPER(A2)), "OK", "Fix")
=IF(EXACT(A2; UPPER(A2)); "OK"; "Fix")

The per-row version: put it in C2 and copy down to see which rows are wrong, not only how many.

=SUMPRODUCT(--NOT(EXACT(A2:A7, UPPER(A2:A7))))
=SUMPRODUCT(--NOT(EXACT(A2:A7; UPPER(A2:A7))))

Same result with NOT and the double minus; longer, but the intent reads like a sentence.

MasterExcel 2021 or later

Uppercase mixed payment references without breaking the numbers

You match the receipts on the bank statement to the open receivables. The payment reference column holds numeric references from the old system, such as 88123, mixed with text references from the new one, such as inv-2041.

The matching file needs the text ones in capitals and trimmed, but the numbers must stay numbers, because your XLOOKUP against the receivables list searches for numeric keys. A plain UPPER would quietly turn 88123 into the text 88123, and every numeric key would come back as not found.

ABCDE
1ReferenceAmountClean reference
2inv-2041€1,210.00€ 1.210,00INV-2041=LET( refs, A2:A7, isText, ISTEXT(refs), IF(isText, UPPER(TRIM(refs)), refs) )=LET( refs; A2:A7; isText; ISTEXT(refs); IF(isText; UPPER(TRIM(refs)); refs) )
38812388123€450.50€ 450,508812388123
4Inv-2044€3,100.00€ 3.100,00INV-2044
58813088130€990.00€ 990,008813088130
6inv-2046€122.25€ 122,25INV-2046
7INV-2050€5,000.00€ 5.000,00INV-2050
8
Formula cell (D2)InputSpilled resultMarks: extra spaceNote: A6 starts with a space. The values in A3 and A5 are real numbers, not text.

Formula in D2

=LET(
  refs, A2:A7,
  isText, ISTEXT(refs),
  IF(isText, UPPER(TRIM(refs)), refs)
)
=LET(
  refs; A2:A7;
  isText; ISTEXT(refs);
  IF(isText; UPPER(TRIM(refs)); refs)
)

Result: INV-2041 / 88123 / INV-2044 / 88130 / INV-2046 / INV-2050INV-2041 / 88123 / INV-2044 / 88130 / INV-2046 / INV-2050

Step by step
  1. LET names the range A2:A7 refs. Nothing is calculated yet; the name is a label for the six cells.
  2. isText is ISTEXT(refs), evaluated for every cell: {TRUE;FALSE;TRUE;FALSE;TRUE;TRUE}. The two numbers, 88123 and 88130, are the FALSE entries.
  3. IF evaluates both branches as arrays. For the TRUE rows it takes UPPER(TRIM(refs)): inv-2041 becomes INV-2041, Inv-2044 becomes INV-2044, the space before inv-2046 is removed and the rest becomes INV-2046, and INV-2050 is unchanged.
  4. For the FALSE rows IF returns refs itself, so 88123 and 88130 come through as the numbers they were. UPPER never sees them.
  5. The result is a six-row array that spills from D2 to D7. D3 and D5 are right-aligned numbers; =COUNT(D2:D7) gives 2, one for 88123 and one for 88130, which a column of text would count as 0.
  6. Type 88140 in A2 and D2 becomes the number 88140; type inv-2060 and D2 becomes INV-2060. The test runs per cell, every time.

Watch out: The mirror image, ISNUMBER(refs) as the test, swaps the branches: the numbers go to UPPER and become text, the text stays as typed. No error appears, and the matching looks fine until receipts you know exist come back as not found.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

UPPER converts anything you give it to text, numbers and dates included; the only way to keep a number is to keep it away from UPPER. ISTEXT decides per cell, and IF with array arguments picks a branch per cell, so one formula handles a mixed column. LET makes the decision readable and evaluates the range once.

Do not use this construction when the whole column is text: =UPPER(TRIM(A2:A7)) does the same with less to read.

The alternative for recurring imports is the uppercase transformation in Power Query; it is cleaner for large files but only updates when you refresh, and in a column that mixes numbers and text you need to check what it does with the numbers before you rely on it.

More to watch out for

  • A number stored as text (a leading apostrophe from an import) is text to ISTEXT, so it stays text. If those must become numbers, add VALUE for the rows that are digits only.
  • D2:D7 must be empty before you enter the formula, otherwise you get #SPILL!.

Variations

=IF(ISTEXT(A2), UPPER(TRIM(A2)), A2)
=IF(ISTEXT(A2); UPPER(TRIM(A2)); A2)

The single-cell version to copy down, for workbooks that will be opened in Excel 2019 or older, where formulas do not spill.

=UPPER(TRIM(A2:A7))
=UPPER(TRIM(A2:A7))

When every reference is text: shorter, and there are no numbers to protect.

Common mistakes

SymptomCauseFix
#REF! after deleting the source columnYou wrote =UPPER(A2) next to the column, then deleted column A, and the new column shows #REF!.Copy the formula column and paste it as values before you remove the original. The formula only points at the source; it does not replace it.
Dates turn into numbers like 46112You used UPPER on a column of dates and got numbers like 46112 instead of dates.A date is a number, and UPPER turns numbers into text. Leave dates alone, or format them with TEXT first if you need an uppercase month name.
UPPER on both sides changes nothingYou added UPPER to both sides of a comparison, =UPPER(A2)=UPPER(B2), expecting it to fix a mismatch, and nothing changed.

If two values still differ, the cause is something else:

  • a trailing space
  • a non-breaking space
  • a number stored as text

Start with TRIM and LEN.