Replace by matching content, not by character position. A replacement can change the length of the string; do not use this to assert the remaining ID format is valid.

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.

=SUBSTITUTE(A2;"_";"-")

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

=IF(LEFT(A2;2)="CA";SUBSTITUTE(A2;"_";"-");A2)

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. This changes Canadian-format order IDs only. Row 3 is left in its original underscore form. It checks a prefix convention, not whether an ID is authentic.

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.

T h e

o f f i c i a l

p a g e

e x p l i c i t l y

c a l l s

S U B S T I T U T E

s e n s i t i v e .

C A

a n d

c a

s h o u l d

n o t

b e

t r e a t e d

a s

i n t e r c h a n g e a b l e ;

r e v i e w

m i s m a t c h e s

a n d

c a p i t a l i z a t i o n

i n

i m p o r t s .

I f

n o

i n t e n d e d

t e x t

a p p e a r s

t o

c h a n g e ,

i n s p e c t

A S C I I

h y p h e n

v e r s u s

t y p o g r a p h i c

d a s h e s ,

u n d e r s c o r e s

v e r s u s

s p a c e s ,

a n d

i n v i s i b l e

c h a r a c t e r s .

T h e

p l a i n

f o r m u l a

c a n n o t

r e m o v e

e v e r y

k i n d

o f

w h i t e s p a c e .

I f

t h e

f o u r t h

a r g u m e n t * *

i s

s e t ,

y o u

a r e

s e l e c t i n g

o n e

o c c u r r e n c e ,

n o t

a

r a n g e

o f

o c c u r r e n c e s .

F o r

a

b l a n k e t

r e p l a c e m e n t

o m i t

i t ;

o t h e r w i s e

c o m p a r e

t h e

c o u n t

o f

d e l i m i t e r s

i n

t h e

s o u r c e .

T h e

v e n d o r

h e l p

l i s t s

g e n e r i c

`

N A M E ! ` ,

`

V A L U E ! `

a n d

`

R E F ! `

p o s s i b i l i t i e s .

I t

d o e s

n o t

p r o v i d e

a

d e t a i l e d

n a t i v e

e r r o r

m a t r i x

f o r

z e r o ,

n e g a t i v e ,

n o n i n t e g e r ,

o r

e x c e s s i v e

w h i c h ;

t h e s e

r e m a i n

t e s t

c a s e s ,

n o t

g u a r a n t e e d

o u t c o m e s .

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 officially uses SUBSTITUTE(text, old_text, new_text, [instance_num]), whereas Zoho names the first and optional final arguments original_text and which and its English examples use semicolons. Both describe selecting one occurrence versus every occurrence. Do not import Excel commas without checking the local app and locale.

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.

[ R E P L A C E

f u n c t i o n

s h e e t / f u n c t i o n s / r e p l a c 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 .

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

[ L E F T

f u n c t i o n

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

—

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 .

[ T R I M

f u n c t i o n

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

—

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.