ysskrishna logoysskrishna

Chapter 4 of 7 · 3 min

Add prefixes, suffixes and text at a position

The Insert tab's four modes: Add prefix, Add suffix, Add prefix and suffix, and Insert at position (1-based).

The Insert tab adds fixed text to every selected cell without a formula. The sidebar shows an INSERT TEXT dropdown, a text field (labelled Prefix when "Add prefix" is chosen, with the placeholder "Text added at the start") and an Apply button.

The four modes

ModeWhat it does1042 with SKU- / -A
Add prefixPrepends textSKU-1042
Add suffixAppends text1042-A
Add prefix and suffixBoth at onceSKU-1042-A
Insert at positionInserts text at a 1-based indexsee below

Insert at position

Positions are 1-based, as the listing explains:

  • Position 1 inserts before the first character.
  • Length + 1 appends at the end.
ValueInsert - at positionResult
2026111752026-1117
2026-111782026-11-17
ABC1234ABC-123

To turn 20261117 into 2026-11-17, run Insert twice: first at 5, then at 8 (the string is one character longer after the first insert). Then format the column as a date if you need real dates.

Formula equivalent, and why the add-on is quicker here

With formulas, prefixing means a helper column with ="SKU-"&A2, fill down, copy, Paste special → Values only, then delete the helper. Insert at position is =LEFT(A2,4)&"-"&MID(A2,5,99). The add-on does it in place in one click, which matters most when the column is in the middle of a layout you don't want to disturb.

Everyday uses

  1. SKUs or ticket keys: SKU-, JIRA-, #.
  2. Units: suffix kg, ms for a report (on text, not on numbers you will calculate with).
  3. URLs: prefix https://www.example.com/products/ to a slug column.
  4. Wrapping values: prefix and suffix with " to quote values for SQL IN (...) lists, or with < and > for emails.
  5. Separators in codes: insert - into fixed-width IDs.

Step by step: build product URLs from slugs

  1. Put sample slugs in A2:A4: 57-google-sheets-tab-manager, 58-table-converter, 59-merge-tools.
  2. Select A2:A4.
  3. Open Insert, choose Add prefix, type https://www.ysskrishna.space/products/, click Apply.
  4. The cells are now full URLs. Sheets usually turns them into links on its own.

Numbers and prefixes

Adding text to a number turns it into text. That is usually what you want for IDs, never for amounts you will sum. If you need a visible unit on a number, use a custom number format instead (Format → Number → Custom number format, e.g. 0" kg").

Practice: build image paths

Column A holds product IDs: 1001, 1002, 1003. You want https://cdn.example.com/img/1001.webp.

  1. Copy the column to B (keep the original).
  2. Select B2:B4, open Insert, choose Add prefix, type https://cdn.example.com/img/, click Apply.
  3. With the same cells selected, choose the suffix option and type .webp, then Apply.
  4. Spot-check one URL by opening it in the browser (with a real CDN path).

The formula version is ="https://cdn.example.com/img/"&A2&".webp", filled down. Use the formula if the IDs will change; use the add-on if you are producing a finished column to hand over.

BeforeAfter prefixAfter suffix
1001https://cdn.example.com/img/1001https://cdn.example.com/img/1001.webp

Leading zeros

IDs such as 00042 are often stored as the number 42 and only displayed with zeros by number formatting. A prefix added to that cell may produce SKU-42, not SKU-00042. If the zeros matter, format the column as plain text (Format → Number → Plain text) and re-enter or re-import the IDs before adding prefixes.

Quick check

  1. Which position appends text to the end of "ABC"?4 (length + 1).

  2. You inserted "-" at 5 in "20261117". Where do you insert the second "-"?At 8, because the value is now one character longer.

  3. Why not suffix " kg" on a weight column you will sum?It turns the numbers into text. Use a custom number format instead.

Continue exploring

ProductGoogle WorkspaceGoogle Sheets Text Power Tools

Google Sheets Text Power Tools

Fix messy sheet text in place. Case, trim, strip, and extract without leaving the grid.
Google Sheets Text Power Tools
Text & Data Conversion
Google Workspace Add-ons
Google Sheets
ProductGoogle WorkspaceGoogle Sheets Tab Manager

Google Sheets Tab Manager

Your workbook outgrew the tab bar. Clean it up in batches.
Google Sheets Tab Manager
Google Workspace Add-ons
Google Sheets
Google Apps Script
CourseCover image for Table Converter for Google Sheets: Import and Export Markdown, JSON, HTML and CSV

Table Converter for Google Sheets: Import and Export Markdown, JSON, HTML and CSV

Import Markdown, JSON, HTML and CSV tables into Google Sheets without broken columns, and export any range as clean Markdown, JSON, HTML or CSV.

Google Sheets Table Converter
Google Sheets
+1

6 chapters · 16 min

BlogCover image for How to Delete Multiple Tabs in Google Sheets at Once (Bulk Hide, Rename, Sort)

How to Delete Multiple Tabs in Google Sheets at Once (Bulk Hide, Rename, Sort)

Google Sheets Tab Manager
Google Workspace Add-ons
+1
2 min read
ProductGoogle WorkspaceGoogle Sheets Merge Tools

Google Sheets Merge Tools

Repeatable merges in Google Sheets, without the misclick marathon.
Google Sheets Merge Tools
Google Workspace Add-ons
Google Sheets
Google Apps Script
CourseCover image for Sheets Tab Manager: Delete, Hide, Rename, Colour and Sort Tabs in Bulk

Sheets Tab Manager: Delete, Hide, Rename, Colour and Sort Tabs in Bulk

Delete multiple tabs in Google Sheets at once, hide and unhide in bulk, rename with find and replace, colour and sort tabs A–Z with the free Sheets Tab Manager.

Google Sheets Tab Manager
Google Sheets
+1

6 chapters · 24 min