Cells in a Range on an Excel Sheet - Practice Coding Problems

Cells in a Range on an Excel Sheet - Problem

String Easy

Excel Sheet Range Generator

Imagine you're working with an Excel spreadsheet and need to programmatically generate all cell references within a given range. In Excel, cells are represented using a combination of:

Column letters: A, B, C, ... Z, AA, AB, ... (like a base-26 numbering system)
Row numbers: 1, 2, 3, ... (simple integers)

For example, cell B3 means column B (2nd column) and row 3.

The Challenge: Given a range string like "A1:F1" (from A1 to F1) or "K1:L2" (from K1 to L2), you need to return all cell references in that rectangular range.

Important: The output should be sorted column-first (like reading a book: left to right, then top to bottom). So for range "A1:B2", the order would be: ["A1", "A2", "B1", "B2"].

Input & Output

example_1.py — Basic Range

$ Input: s = "K1:L2"

› Output: ["K1","K2","L1","L2"]

💡 Note: The range spans 2 columns (K, L) and 2 rows (1, 2). We generate column-first: all K cells first (K1, K2), then all L cells (L1, L2).

example_2.py — Single Row Range

$ Input: s = "A1:F1"

› Output: ["A1","B1","C1","D1","E1","F1"]

💡 Note: This is a horizontal range spanning 6 columns but only 1 row. Each column contributes exactly one cell.

example_3.py — Single Cell

$ Input: s = "A2:A2"

› Output: ["A2"]

💡 Note: Edge case where start and end are the same cell. The range contains only one cell: A2.

Constraints

The length of s is in range [1, 7]
s consists of uppercase English letters, digits and ':'
s is a valid range string in the format "<col1><row1>:<col2><row2>"
1 <= col1 <= col2 <= 26 (only single letters A-Z)
1 <= row1 <= row2 <= 9
The range is guaranteed to be valid (col1 ≤ col2 and row1 ≤ row2)

Visualization

Tap to expand

Understanding the Visualization

Parse the Range

Split 'A1:C2' into start coordinates (A,1) and end coordinates (C,2)

Column A Processing

Generate all cells in column A: A1, A2 (rows 1 to 2)

Column B Processing

Generate all cells in column B: B1, B2 (rows 1 to 2)

Column C Processing

Generate all cells in column C: C1, C2 (rows 1 to 2)

Combine Results

Final ordered list: [A1, A2, B1, B2, C1, C2]

Key Takeaway

🎯 Key Insight: Excel ranges follow column-first ordering, not row-first. This is like reading a book where you finish each page (column) completely before moving to the next page, rather than reading line by line across multiple pages.

Asked in

G Google 15 ⊞ Microsoft 25 a Amazon 12 f Meta 8

This problem is solved optimally using systematic generation. Parse the range string to extract coordinates, then iterate column-first through each position in the rectangular range. The key insight is that Excel ranges are ordered by columns first, then rows - like reading a spreadsheet left to right, top to bottom within each column. Time complexity is O(R × C) where R and C are the dimensions of the range.

Common Approaches

Approach	Time	Space	Notes
✓ Systematic Generation (Optimal)	O(R × C)	O(R × C)	Parse range and generate cells column by column, row by row

Systematic Generation (Optimal) — Algorithm Steps

Parse the input string to separate start cell (col1, row1) and end cell (col2, row2)
Extract column letters and convert them to numeric values for easier iteration
Extract row numbers directly from the string
Iterate through each column from col1 to col2
For each column, iterate through each row from row1 to row2
Convert column numbers back to letters and combine with row numbers
Add each generated cell reference to the result list

Visualization

Tap to expand

Step-by-Step Walkthrough

Parse Input Range

Split 'A1:C2' into start cell 'A1' and end cell 'C2'

Extract Coordinates

Start: col='A'(65), row=1 | End: col='C'(67), row=2

Generate Column A

Iterate rows 1-2 in column A: ['A1', 'A2']

Generate Column B

Iterate rows 1-2 in column B: ['B1', 'B2']

Generate Column C

Iterate rows 1-2 in column C: ['C1', 'C2']

Final Result

Combine all: ['A1', 'A2', 'B1', 'B2', 'C1', 'C2']

Code -

solution.c — C

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

char** cellsInRange(char* s, int* returnSize) {
    // Find colon position
    char* colon = strchr(s, ':');
    *colon = '\0';
    
    char* start = s;
    char* end = colon + 1;
    
    // Extract coordinates
    char startCol = start[0];
    int startRow = atoi(start + 1);
    
    char endCol = end[0];
    int endRow = atoi(end + 1);
    
    // Calculate result size
    int cols = endCol - startCol + 1;
    int rows = endRow - startRow + 1;
    *returnSize = cols * rows;
    
    char** result = (char**)malloc(*returnSize * sizeof(char*));
    int idx = 0;
    
    // Generate all cells
    for (char col = startCol; col <= endCol; col++) {
        for (int row = startRow; row <= endRow; row++) {
            result[idx] = (char*)malloc(10 * sizeof(char));
            sprintf(result[idx], "%c%d", col, row);
            idx++;
        }
    }
    
    return result;
}

int main() {
    char input[100];
    fgets(input, sizeof(input), stdin);
    
    // Remove newline and quotes
    input[strcspn(input, "\n")] = 0;
    if (input[0] == '"') {
        memmove(input, input + 1, strlen(input));
        input[strlen(input) - 1] = '\0';
    }
    
    int returnSize;
    char** result = cellsInRange(input, &returnSize);
    
    printf("[");
    for (int i = 0; i < returnSize; i++) {
        printf("\"%s\"", result[i]);
        if (i < returnSize - 1) printf(",");
        free(result[i]);
    }
    printf("]\n");
    
    free(result);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(R × C)

Where R is the number of rows and C is the number of columns in the range. We generate exactly R×C cells.

✓ Linear Growth

Space Complexity

O(R × C)

We store all R×C cell references in the result list.

✓ Linear Space

38.7K Views

Medium Frequency

~8 min Avg. Time

1.8K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Systematic Generation (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler