Replace by known position rather than by searching for a specific character. A fixed-position rule can silently modify the wrong character if the source format changes.

In a reseller or inventory worksheet, raw order IDs can contain underscores, date segments, or other embedded information. Keep the imported original in one column and calculate changes in a separate empty column so a reviewer can compare before and after. A formula cannot validate that the original input came from a trusted source.

Import the review CSV batches/zoho-sheet/2026-10-11T0647Z-text-operations-batch07/text-operations-practice.csv into a new, empty sheet starting at A1. The header is in row 1 and example records occupy rows 2–9. Keep column A in text format so underscores and leading zeros remain intact. An import wizard can reinterpret text, so compare A2 visually with the table before entering formulas.

Use an empty cell such as F2 for outputs; never enter results over the source range A2:D9. Numbers and codes in the practice table are fictitious.

1. Confirm A2 contains the literal text CA_2026_0041, not a number or a formula result.
2. Select the empty result cell F2, then enter the formula below exactly as written.
3. Compare the result to the stated expected value and the source text; copying it down should change only relative cell references.

=REPLACE(A2;3;1;"-")

Expected F2 result: CA-2026_0041. This outcome was checked with independent Python string processing; no Zoho Sheet function was run.

More independently checked examples

=REPLACE(REPLACE(A2;3;1;"-");8;1;"-")

In row 2, the independently calculated expected output is CA-2026-0041. Applying the same expression to row 3 yields US-2026-0087 for the source US_2026_0087. The nested expression changes underscores at fixed positions 3 and 8. It is convenient only when every ID obeys the same layout. If imported IDs vary in length, prefer SUBSTITUTE, or locate positions with FIND before replacing.

Build the calculation in stages in separate helper cells before relying on its nested form. For example, verify the simple formula on row 2, inspect the intermediate substring or position, and only then copy down. If the source layout changes, update the validation logic rather than blindly shifting offsets.

If the expected output differs in Zoho Sheet, first verify imported cells (text versus number), quote marks, exact punctuation, references and semicolon argument separators. The official Zoho page includes generic diagnostic error categories such as #NAME!, #VALUE!, #REF! and #N/A!; their mere presence in the help page does not prove that every formula variant shown below triggers a particular error.

s t a r t _ p o s i t i o n

a n d

l e n g t h

a r e

d i s t i n c t .

A

s t a r t

o f

4

a n d

l e n g t h

4

o v e r w r i t e s

f o u r

c h a r a c t e r s

s t a r t i n g

a t

p o s i t i o n

4 ;

i t

d o e s

n o t

m e a n

“ e n d

a t

p o s i t i o n

4 . ”

T h e

v e n d o r

s p e c i f i c a l l y

l i s t s

`

V A L U E ! `

w h e n

s t a r t _ p o s i t i o n

o r

l e n g t h

i s

n e g a t i v e .

T h e

s o u r c e

d o e s

n o t

f u l l y

d o c u m e n t

z e r o ,

f r a c t i o n a l

p o s i t i o n s

o r

b o u n d s

r u l e s ,

s o

a v o i d

a s s e r t i n g

t h e i r

e x a c t

r e s u l t s

b e f o r e

n a t i v e

t e s t s .

F o r

l e n g t h

I D s ,

c h e c k

l e n g t h

a n d

d e l i m i t e r

p o s i t i o n s

b e f o r e

u s i n g

a

f i x e d

o f f s e t .

A n

a p p a r e n t l y

p l a u s i b l e

c h a n g e d

v a l u e

c a n

s t i l l

b e

w r o n g .

A

b r o k e n

s o u r c e

r e f e r e n c e

m a y

y i e l d

`

R E F ! ` ;

a n

i n c o r r e c t

f u n c t i o n

s p e l l i n g

o r

m i s s i n g

q u o t a t i o n

m a r k s

m a y

y i e l d

`

N A M E ! `

a s

d e s c r i b e d

b y

Z o h o ' s

g e n e r a l

e r r o r

t a b l e .

The safest repair workflow is to copy a troublesome input into a clean text-only cell, test the simplest vendor-documented expression there, then examine one argument at a time. Do not silently substitute invented values for absent data. For client records, keep source and corrected values for auditability.

Microsoft Excel documents REPLACE(old_text, start_num, num_chars, new_text) with comma-separated arguments in English guidance; Zoho uses REPLACE(original_text; start_position; length; new_text). Excel also documents modern Unicode-surrogate-pair improvements tied to a compatibility version; Zoho’s cited reference does not establish the same Unicode counting behaviour.

Google Sheets and LibreOffice Calc have similarly named text functions, but identical names do not establish identical parser rules, Unicode indexing, edge-case errors or export preservation. For platform migration, test the same sample data on each target before writing a claim of equivalent results.

This guide uses Zoho’s currently public Sheet web documentation checked on 2026-10-11. The cited Zoho reference does not identify the first release supporting this function or establish equal behaviour in offline desktop versions, mobile apps, every workspace plan, or exported workbooks. Those are not verified. Likewise, whether a particular device uses semicolons under the user’s locale must be checked in its formula editor.

[ S U B S T I T U T E

f u n c t i o n

s h e e t / f u n c t i o n s / s u b s t i t u t e / )

—

e d i t o r i a l

l i n k s

a r e

o n l y

u n t i l

c o r r e s p o n d i n g

p a g e s

a r e

a p p r o v e d

a n d

d e p l o y e d .

[ F I N D

f u n c t i o n

s h e e t / f u n c t i o n s / f i n d / )

—

e d i t o r i a l

l i n k s

a r e

o n l y

u n t i l

c o r r e s p o n d i n g

p a g e s

a r e

a p p r o v e d

a n d

d e p l o y e d .

[ M I D

f u n c t i o n

s h e e t / f u n c t i o n s / m i d / )

—

e d i t o r i a l

l i n k s

a r e

o n l y

u n t i l

c o r r e s p o n d i n g

p a g e s

a r e

a p p r o v e d

a n d

d e p l o y e d .

[ S E A R C H

f u n c t i o n

s h e e t / f u n c t i o n s / s e a r c h / )

—

e d i t o r i a l

l i n k s

a r e

o n l y

u n t i l

c o r r e s p o n d i n g

p a g e s

a r e

a p p r o v e d

a n d

d e p l o y e d .

• Zoho Sheet — function documentation
• Zoho Sheet — Text function category
• Zoho Sheet — all functions index

Verification status: Official documentation checked; example outcomes were independently reasoned or arithmetically checked where applicable. Not executed in Zoho Sheet.

• No genuine Zoho Sheet screenshots are included; this guide does not use mock application images.