Cells in a Range on an Excel Sheet — Problem & Solution
Cells in a Range on an Excel Sheet solution explained: step-by-step approach, complexity and code in Python, Java, C++ and JavaScript. An easy problem.
Cells in a Range on an Excel Sheet is an easy coding problem on strings, asked in Amazon and Microsoft interviews.
- Difficulty: Easy
- Topics: Strings
- Asked at: Amazon, Microsoft
- Time limit: 2 s
- Memory limit: 256 MB
- Languages: JavaScript, TypeScript, Python, Java, C++, C, C#, Go, Kotlin, Swift, Rust, PHP and Ruby
Problem statement
A spreadsheet cell is named by its column letter followed by its row number, like "B3" (column B, row 3).
You are given a range s = "<col1><row1>:<col2><row2>", with col1 <= col2 and row1 <= row2. Return every cell x with col1 <= column(x) <= col2 and row1 <= row(x) <= row2, as strings, ordered first by column and then by row.
Example 1
Input: s = "B2:C3"
Output: ["B2","B3","C2","C3"]
Explanation: Column B's cells come first, top to bottom, then column C's.
Example 2
Input: s = "A1:F1"
Output: ["A1","B1","C1","D1","E1","F1"]
Example 3
Input: s = "Z9:Z9"
Output: ["Z9"]
Constraints
s.length == 5'A' <= s[0] <= s[3] <= 'Z''1' <= s[1] <= s[4] <= '9's[2] == ':'s consists of uppercase English letters, digits and ':'
Hints for Cells in a Range on an Excel Sheet
Hint 1
Read the four characters that matter: the two column letters at indices 0 and 3, and the two row digits at indices 1 and 4.
Hint 2
Two nested loops cover the rectangle — which loop goes outside decides the order.
Hint 3
Columns must change slowest, so loop over columns on the outside and rows inside.
How to solve Cells in a Range on an Excel Sheet
The range is a rectangle of letters × digits; enumerate it with the column loop outermost so the output is column-major.
Approach
- For each letter
cfroms[0]tos[3]: - For each digit
rfroms[1]tos[4], append the two-character stringc + r. - Return the list.
Complexity
- Time —
O((col2 - col1 + 1) · (row2 - row1 + 1)) - Space —
O(1) beyond the output
Pitfalls
- Looping rows on the outside produces row-major order, which is wrong here.
- Rows are single digits
1–9, so each cell name is exactly two characters.
Reference solution
Python
from typing import List
def cellsInRange(s: str) -> List[str]:
return [chr(c) + str(r)
for c in range(ord(s[0]), ord(s[3]) + 1)
for r in range(int(s[1]), int(s[4]) + 1)]Java
public static String[] cellsInRange(String s) {
List<String> out = new ArrayList<>();
for (char c = s.charAt(0); c <= s.charAt(3); c++) {
for (char r = s.charAt(1); r <= s.charAt(4); r++) {
out.add("" + c + r);
}
}
return out.toArray(new String[0]);
}C++
vector<string> cellsInRange(string s) {
vector<string> out;
for (char c = s[0]; c <= s[3]; c++) {
for (char r = s[1]; r <= s[4]; r++) {
out.push_back(string{c, r});
}
}
return out;
}JavaScript
var cellsInRange = function(s) {
var out = [];
for (var c = s.charCodeAt(0); c <= s.charCodeAt(3); c++) {
for (var r = s.charCodeAt(1); r <= s.charCodeAt(4); r++) {
out.push(String.fromCharCode(c) + String.fromCharCode(r));
}
}
return out;
};Also on the editorial tab: C, C#, Go, Kotlin, PHP, Ruby, Rust, Swift, TypeScript.
All 515 strings problems · the whole catalogue
Learn the technique: Strings