Cells in a Range on an Excel Sheet

Easy
1755.

Cells in a Range on an Excel Sheet

Cells in a Range on an Excel Sheet is an easy coding problem on strings, asked in Amazon and Microsoft interviews.

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 ':'
Write your solution here. Running it against the tests needs a free account — what you type comes with you.Sign in to run
/**
 * @param {string} s
 * @return {string[]}
 */
var cellsInRange = function(s) {
    // Write your code here
    
};

Sign in to run your own test cases

Sign in

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

  1. 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.

  2. Hint 2

    Two nested loops cover the rectangle — which loop goes outside decides the order.

  3. 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

  1. For each letter c from s[0] to s[3]:
  2. For each digit r from s[1] to s[4], append the two-character string c + r.
  3. 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