PHONETIC

What it does

PHONETIC(reference)

PHONETIC(reference) returns the furigana stored alongside the text in a cell.

Microsoft's description: "Extracts the phonetic (furigana) characters from a text string."

The quoted one-line description is Microsoft's text, from its support article https://support.microsoft.com/en-us/office/phonetic-function-9a329dac-0c0f-42f8-9a55-639086988554.

Understanding this function requires one fact about Japanese text in Excel that has no analogue in the rest of the worksheet surface: a cell can carry a second string that is not its value. When Japanese text is typed through an IME, the IME knows the reading that produced the kanji, and Excel stores that reading as furigana — a phonetic annotation attached to the cell. It is not part of the cell's text value, it is not visible unless furigana display is switched on, and no other function can see it.

PHONETIC is the only worksheet function that reads it.

This makes PHONETIC structurally unlike every other text function. LEFT, UPPER, LEN and the rest are pure functions of a string: given the same input they compute the same output anywhere, on any machine, in any locale. PHONETIC is not a computation on its argument at all — it is a retrieval of stored metadata, and the metadata was created by the input method at the time the text was typed. Two cells displaying identical kanji can return different furigana, or one can return furigana and the other nothing, depending entirely on how each was entered. Text produced by a formula, imported from a file, or pasted as values generally carries no furigana at all.

Three further constraints, all documented:

1. The system region must be set to a Far East language — Japanese, Chinese or Korean — for the function to work.
2. A multi-cell reference is reduced to its upper-left cell; the function does not aggregate.
3. A non-contiguous reference is an error, not a reduction.

Arguments

reference — required, exactly one. Microsoft's description: "Text string or a reference to a single cell or a range of cells that contain a furigana text string."

The name reference is doing real work here. Furigana is a property of a cell, so the useful form of the call is a reference; a literal text string has no furigana attached to it, because there was never an IME session that produced it. The documentation admits a text string as an argument form, but what a text literal could usefully return is not something this Handbook can state.

The multi-cell form is admitted and reduced: with a range, the answer comes from the upper-left cell only. This is the same reduction CELL("contents", …) performs, and it is worth knowing because it is silent — a range argument does not error, it just quietly ignores everything but one cell.

Try it

Input cells in this preview do not carry phonetic annotations. OneCalc therefore returns #N/A for this example.

Quirks

Result and edge cases

Returns Text — the stored furigana, which may be an empty string when the cell has none.

• No furigana means no result, not an error. A cell of Latin text, a formula result, an imported value: none of these carries the annotation, and the expected answer is empty text. The Handbook has not confirmed this against a live build.
• Formula results carry no furigana. PHONETIC on a cell containing =A1&B1 reads the concatenation's stored annotation, and a concatenation does not produce one. This is the most common disappointment with the function: it works on typed data and not on derived data.
• The annotation can be edited independently of the text. Excel's furigana editing UI lets a user change the reading without changing the kanji, which means PHONETIC's answer is not derivable from the cell's value even in principle.
• Katakana versus hiragana. Excel has a furigana type setting; which script the stored annotation uses is a property of how the cell was configured. What PHONETIC returns for each setting is not established here.
• Locale dependence is total. Outside a Far East region setting, the documented requirement is not met and the function's behaviour is not something this page can state.

Errors

• #N/A — documented, for a reference to non-adjacent cells (a multi-area reference).

The Handbook has read Microsoft's page and does not have a further documented error table to reproduce. In particular, what happens when the system region requirement is unmet — an error, an empty string, or a #NAME? because the function is not exposed — is not established here, and it is the first thing this page would probe.

How to implement

Notes for implementers

1. This is not a text algorithm. There is no rule that converts kanji to a reading — the mapping is ambiguous, which is exactly why the IME stores the reading rather than deriving it. Any implementation that tries to generate furigana is implementing a different function.
2. It requires a cell metadata channel. An engine whose cell model is "a value plus a format" cannot hold furigana and therefore cannot implement PHONETIC at all. Declining is the correct response.
3. The multi-cell reduction is upper-left, silently. Not an error, not an aggregate.
4. Non-contiguous is #N/A, distinct from the reduction case, and the two must be told apart.
5. Any behavioural claim must carry a locale axis, in the same way INFO's claims must carry platform and build (claim language and honesty).

Rust code

12 phonetic_fn "PHONETIC" (1, 1) [F "reference"] |s, a, sv| { phonetic(s, a, sv) }

/// PHONETIC under a language with no phonetic guide is #N/A, for a text cell and a blank one
/// alike (Excel under en-ZA: PHONETIC of a blank cell, and of a cell holding x in the catalogue
/// oracle's capture). Under Japanese it is not modelled.
fn phonetic(site: &Site<'_>, a: &[CalcValue], sv: &mut EvalServices<'_>) -> Step<CalcValue> {
    let (_, profile) = profile(site)?;
    if matches!(profile.id, LocaleId::JaJp) {
        return Err(site.not_modelled(None));
    }
    let _ = (a, sv);
    fail(WorksheetErrorCode::NA)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• ASC and DBCS are the other locale-gated East-Asian text functions in Excel's surface, converting between half-width and full-width forms. Unlike PHONETIC, they are pure functions of their argument; they are gated by locale but not by stored metadata.
• FINDB, LEFTB, LENB, MIDB, REPLACEB, RIGHTB, SEARCHB are the byte-variant text functions whose behaviour differs under DBCS locales — the other place in Excel where locale changes what a text function does. They are byte-counting variants, not metadata readers.
• No other function reads furigana. There is no ISPHONETIC, no way to test whether a cell has an annotation other than calling PHONETIC and checking for an empty result, and no way to write furigana from a formula. The read is one-way and the surface is one function wide.
• CELL is the nearest structural analogue in this Handbook: another function that returns stored workbook state rather than computing on a value.

Named variants

No named variants are recorded yet.