How to convert a score like 18/20 to a percentage in Excel
Excel formula
=TEXTBEFORE(B2,"/")/TEXTAFTER(B2,"/")Split the text at the slash and divide: =TEXTBEFORE(B2,"/")/TEXTAFTER(B2,"/"), formatted as a percentage.
- Result on the example
- one result per row in
C2, filled down toC6 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in C2, filled down to C6. Change any input and the result updates, or edit the formula to experiment.
How it works
The score is text, so it can't be divided directly. TEXTBEFORE returns the points before the slash and TEXTAFTER the points after it. Dividing them converts both to numbers: 18 / 20 = 90%.
In Google Sheets, SPLIT breaks the text at the slash and INDEX picks each part.
=TEXTBEFORE(B2,"/")/TEXTAFTER(B2,"/")Result0.9
B2"18/20"The value in B2.
TEXTBEFORE(B2,"/")"18"TEXTBEFORE: Text before a delimiter.- text
B2"18/20" - delimiter
"/"
- text
TEXTAFTER(B2,"/")"20"TEXTAFTER: Text after a delimiter.- text
B2"18/20" - delimiter
"/"
- text
TEXTBEFORE(B2,"/")/TEXTAFTER(B2,"/")0.9The / operator divides.
In Google Sheets
Google Sheets needs a different formula: =INDEX(SPLIT(B2,"/"),1)/INDEX(SPLIT(B2,"/"),2). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =LEFT(B2,FIND("/",B2)-1)/MID(B2,FIND("/",B2)+1,10) | 90%, 84%, 70%… | Works in every Excel version: FIND locates the slash, LEFT and MID take the parts. |
Common mistakes
- Typing 3/4 into a cell turns it into a date (March 4) in US settings. Format the column as Text first, or keep points and maximum in separate columns.
- Spaces around the slash ("18 / 20") are fine for the division, because Excel ignores spaces around a number in text.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Convert a score like 18/20 to a percentage
C2, turn the score text like "18/20" into a percentage, then fill down to C6.Questions
How do I average the percentages?
Average column C, or for a weighted result divide total points by total maximum points.
How do I write the score as text from two numbers?
Join them: =B2&"/"&C2.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.