Data Preparation
Excel's text manipulation functions map directly to Python string methods in VisiData. This page shows every common data preparation formula side by side with its VisiData equivalent.
Learning Focus
Python string methods replace Excel text functions one-for-one. Once you know str.strip() = TRIM and str.upper() = UPPER, the rest follows the same pattern. Practice with the employees dataset to cement the mappings.
Text Function Reference
| Excel / Google Sheets | VisiData Python | Description |
|---|---|---|
=LEFT(A2, 3) | = name[:3] | First 3 characters |
=RIGHT(A2, 3) | = name[-3:] | Last 3 characters |
=MID(A2, 2, 3) | = name[1:4] | Substring (0-indexed in Python) |
=LEN(A2) | = len(name) or z# column type | String length |
=TRIM(A2) | = value.strip() | Remove leading/trailing spaces |
=LTRIM(A2) | = value.lstrip() | Remove leading spaces only |
=RTRIM(A2) | = value.rstrip() | Remove trailing spaces only |
=UPPER(A2) | = value.upper() | Convert to uppercase |
=LOWER(A2) | = value.lower() | Convert to lowercase |
=PROPER(A2) | = value.title() | Capitalize each word |
=SUBSTITUTE(A2, "-", "/") | = value.replace('-', '/') | Replace all occurrences |
=REPLACE(A2, 1, 3, "XYZ") | = 'XYZ' + value[3:] | Replace by position |
=TEXT(A2, "0.00") | = f"{value:.2f}" | Format as string |
=VALUE(A2) | Cast with # or % key | Convert text to number |
=FIND("@", A2) | = value.find('@') | Position of character (0-indexed) |
=SEARCH("@", A2) | = value.lower().find('@') | Case-insensitive find |
=TEXTJOIN(", ", TRUE, A2:A5) | = ', '.join(list_col) | Join array of values |
=CONCATENATE(A2, B2) | = col_a + col_b | Combine columns |
=A2 & " " & B2 | = col_a + ' ' + col_b | Concat with separator |
=TEXTBEFORE(A2, "@") | = value.split('@')[0] | Extract before delimiter |
=TEXTAFTER(A2, "@") | = value.split('@')[-1] | Extract after delimiter |
=REPT(A2, 3) | = value * 3 | Repeat string N times |
=EXACT(A2, B2) | = col_a == col_b | Case-sensitive comparison |
=T(A2) | No equivalent needed | Column type ~ handles this |
Practical Examples on Employee Data
vd ~/github/practice-folder/visidata/01-loading/01-employees.csv
TRIM — Remove Whitespace
# Excel: =TRIM(A2)
# VisiData:
=
# Enter: name.strip()
# Name: name_clean
UPPER / LOWER / PROPER
# Excel: =UPPER(A2)
=
# Enter: department.upper()
# Excel: =LOWER(A2)
=
# Enter: email.lower()
# Excel: =PROPER(A2)
=
# Enter: city.title()
SUBSTITUTE — Replace Text
# Excel: =SUBSTITUTE(A2, "-", "_")
=
# Enter: employee_id.replace('-', '_')
# Replace multiple: chain .replace()
=
# Enter: phone.replace('-', '').replace(' ', '').replace('(', '').replace(')', '')
LEFT / RIGHT / MID
# Excel: =LEFT(A2, 3) — first 3 chars of department code
=
# Enter: department[:3]
# Excel: =RIGHT(A2, 4) — last 4 chars (e.g., year from date string)
=
# Enter: start_date[-4:]
# Excel: =MID(A2, 5, 3) — 3 chars starting at position 5
=
# Enter: employee_id[4:7] # Python is 0-indexed
FIND — Position of Character
# Excel: =FIND("@", A2)
=
# Enter: email.find('@')
# Returns: character position (0-indexed, -1 if not found)
CONCATENATE / TEXTJOIN
# Excel: =A2 & " " & B2
=
# Enter: first_name + ' ' + last_name
# Excel: =TEXTJOIN(", ", TRUE, A2:A5)
# In VisiData — for frequency list aggregator:
# Move to column → set aggregator
+ list
# Then in pivot: shows comma-separated list
Type Casting (=VALUE equivalent)
Cast column types using keyboard shortcuts instead of formulas:
| Excel | VisiData | Key |
|---|---|---|
=VALUE(A2) → integer | Cast to int | # |
=VALUE(A2) → decimal | Cast to float | % |
| Format as Date | Cast to date | @ |
| Format as Currency | Cast to currency | $ |
| Format as Text | Cast to str | ~ |
vd ~/github/practice-folder/visidata/01-loading/01-employees.csv
# Cast 'salary' string to float
# Move to 'salary' column → press %
# Cast 'start_date' string to date
# Move to 'start_date' column → press @
Removing Duplicates
| Excel | VisiData |
|---|---|
| Data → Remove Duplicates | Shift+F → identify count > 1 → delete extras |
| Remove duplicates on one column | Shift+F on that column → filter count > 1 → gd |
vd ~/github/practice-folder/visidata/01-loading/01-employees.csv
# Find duplicates on 'employee_id'
# Move to 'employee_id' column
Shift+F # open frequency table
# Cast 'count' column to int
#
# Filter rows where count > 1
z|
# Enter: count > 1
# These are the duplicate values — press Enter to drill into source rows
Enter
# Select the duplicate rows: s or gs
# Delete extras: gd
Filling Null Values
| Excel | VisiData |
|---|---|
| Fill down | f — fill null cells with non-null value above |
| Replace blanks with value | ge — bulk edit selected cells |
| IFERROR to default | = value or 'default' |
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# Fill null region values from above
# Move to 'region' column
f
# Set all null amounts to 0
z|
# Enter: amount is None
ge
# Enter: 0
Hands-On Practice
vd ~/github/practice-folder/visidata/01-loading/01-employees.csv
# 1. Create TRIM equivalent — clean name column:
=
# Enter: name.strip().title()
# Name: name_clean
# 2. Create email domain column (TEXTAFTER equivalent):
=
# Enter: email.split('@')[-1]
# Name: email_domain
# 3. Create employee initials (LEFT equivalent):
=
# Enter: first_name[0] + last_name[0]
# Name: initials
# 4. Cast salary to float: move to 'salary' → %
# 5. Fill any null department values:
# Move to 'department' → f
# 6. Press Ctrl+S → save as /tmp/employees_prepared.csv