# Salesforce 15 vs 18 Character IDs: Convert Safely and Fix Broken Joins

Use Salesforce's 18-character record ID when data moves into spreadsheets, warehouses or integrations. Convert the original 15-character value before any process changes its letter case, and preserve the returned capitalization. Conversion fixes a representation problem; it does not prove that the record exists, belongs to the intended object or is accessible in the target org.

Canonical URL: https://www.martechdevs.com/blog/salesforce-15-vs-18-character-id

By [Jafrin Akter Alo](https://www.martechdevs.com/authors/jafrin-akter-alo)

Data Engineer and Co-founder at [Martechdevs](https://www.martechdevs.com) and [Growth and Analytics Partners](https://www.growthandanalytics.com)

[LinkedIn](https://linkedin.com/in/jafrinalo)

Last updated: 2026-10-25

Next step: [Salesforce Implementation Cost: Scope, Partner Fees and a First-Year Budget](https://www.martechdevs.com/blog/salesforce-implementation-cost)

A broken CRM join can be more dangerous than an obvious import error. An unmatched row stops the process; an incorrectly matched row can attach a payment, account owner or customer activity to the wrong record. Start by preserving the original values and identifying where they changed.

This guide covers Salesforce record IDs, not arbitrary customer identifiers. For a wider migration, use the [Salesforce-to-HubSpot migration plan](https://www.martechdevs.com/blog/salesforce-to-hubspot-migration) to preserve relationships and reconciliation alongside the ID conversion.

## What is the difference between a 15-character and 18-character Salesforce ID?

The 15-character form is case-sensitive. The 18-character form appends three characters that encode the capitalization of the first fifteen, allowing case-insensitive applications to distinguish otherwise similar strings. Both forms represent the same underlying record. Salesforce's Object Reference calls the longer form **case-safe** and warns against manually changing its capitalization. [Salesforce ID field reference](https://resources.docs.salesforce.com/latest/latest/en-us/sfdc/pdf/object_reference.pdf).

For your data contract, distinguish three states:

| Input state | What it tells you | Appropriate next action |
| --- | --- | --- |
| Original 15-character ID with preserved case | Enough information for case-safe conversion | Convert before an external join or case-changing transformation |
| Original canonical 18-character ID | Already contains the case suffix | Preserve it and check the target record relationship |
| A value with lost case, missing characters or an inconsistent suffix | The original identity may be damaged | Quarantine it and recover from the authoritative source |

“Case-safe” describes comparison behavior. It does not authorize a pipeline to lowercase every ID before sending values back to Salesforce. Retain the canonical source value even if an external database compares strings without distinguishing letter case.

A local suffix check also cannot detect every typo. Changing one digit to another does not change the capitalization pattern. A value can pass a shape-and-suffix test while pointing to no record or a different record. Record existence and intended ownership need separate checks against the right Salesforce org.

## How should you get the 18-character ID from Salesforce?

Prefer an export that already supplies the longer ID, or expose a Text formula field using `CASESAFEID(Id)` for a repeatable report. Salesforce's conversion guide recommends the object formula field for ongoing use and provides the Lightning setup path. [Salesforce conversion instructions](https://help.salesforce.com/s/articleView?id=000385066&language=en_US&type=1).

For an administrator-owned report:

1. Open **Setup → Object Manager** and select the intended object.
2. Open **Fields & Relationships → New**.
3. Choose **Formula** and a **Text** return type.
4. Enter the formula below.
5. Set the intended field visibility, save the field and add it to the report.

```text
CASESAFEID(Id)
```

Label the output clearly, such as `Salesforce Record ID 18`, and retain the object name and source environment in the extract. Test the report as the user who will actually export it; an administrator seeing the field does not establish that the operations team can use it.

Salesforce reports can internally convert a record-ID filter to the longer form while showing the shorter ID in the column. Adding the formula field as a column makes the full representation explicit. A changed filter display alone does not mean a second record was created. [Report filter and column behavior](https://help.salesforce.com/s/articleView?id=005131264&language=en_US&type=1).

Do not create another formula field for every integration if one governed field already serves the purpose. Record the authoritative export route, owner and expected format in the [CRM property register](https://www.martechdevs.com/blog/crm-property-ownership).

## Can you convert Salesforce IDs locally without uploading the file?

Yes. The conversion uses the original string's capitalization, so it can run locally without contacting Salesforce. The function below accepts an original 15-character value or an already canonical 18-character value. It rejects whitespace, non-ASCII characters, incorrect lengths and inconsistent suffixes instead of silently repairing them.

The suffix calculation follows the algorithm in Salesforce's [conversion reference](https://help.salesforce.com/s/articleView?id=000385066&language=en_US&type=1): three groups of five characters, with uppercase positions represented by a bit mask. The code is a local implementation with stricter input checks than the reference bookmarklet.

```python
import re

_SUFFIX = "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345"


def salesforce_id18(value):
    """Normalize preserved-case IDs; validate shape, not record existence."""
    if not isinstance(value, str):
        raise ValueError("ID must be a string")
    if not re.fullmatch(r"[A-Za-z0-9]{15}(?:[A-Za-z0-9]{3})?", value):
        raise ValueError("ID must contain exactly 15 or 18 ASCII letters/digits")

    base = value[:15]
    suffix = ""
    for start in (0, 5, 10):
        mask = sum(
            1 << offset
            for offset, char in enumerate(base[start:start + 5])
            if "A" <= char <= "Z"
        )
        suffix += _SUFFIX[mask]

    canonical = base + suffix
    if len(value) == 18 and value != canonical:
        raise ValueError("ID suffix or letter case is inconsistent")
    return canonical


assert salesforce_id18("00300000003T2PG") == "00300000003T2PGAA0"
assert salesforce_id18("00300000003T2PGAA0") == "00300000003T2PGAA0"
```

The sample ID pair comes from Salesforce's [Data Loader error guide](https://help.salesforce.com/s/articleView?id=000387915&language=en_US&type=1). It is a reference fixture, not a record to update in your org.

Keep the function strict at the boundary. If a CSV field contains surrounding spaces, retain the original cell, identify the upstream cause, and apply a separately approved cleanup rule before conversion. Do not bury trimming, uppercasing and ID conversion inside one unexplained operation.

This helper establishes only string shape and suffix consistency. It does not validate object prefixes, ownership, sharing, deletion state or target-org existence. It intentionally rejects an altered-case 18-character input rather than trying to guess a repaired record identity.

## Why can a case-insensitive join return the wrong record?

A case-insensitive join can treat two distinct 15-character strings as equal because their only difference is capitalization. Convert both sides to canonical 18-character values before joining, while the original case still exists.

Use the following synthetic strings to test your pipeline. They demonstrate comparison behavior and are not claimed to exist in Salesforce:

```python
left = "00100000000Abcd"
right = "00100000000aBcd"

assert left != right
assert left.lower() == right.lower()

left18 = salesforce_id18(left)
right18 = salesforce_id18(right)

assert left18 != right18
assert left18.lower() != right18.lower()
```

The suffix preserves a distinction that a case-insensitive comparison of the short forms loses. It is not a cryptographic integrity check, and it does not make every transformation safe.

For a real reconciliation, compare join cardinality before and after normalization:

| Result | Meaning to investigate | Safe handling |
| --- | --- | --- |
| Exactly one intended match | Candidate relationship resolved | Confirm object and source-org scope |
| No match | Missing source row, damaged ID, wrong environment or legitimate absence | Retain an unmatched-row queue |
| Multiple matches | Duplicate dimension rows or an incorrect join key | Fix the duplication or relationship grain |
| One match after lowercasing a short ID | Potential case collision | Re-export preserved-case IDs and repeat the join |

Do not use a `DISTINCT` operation to conceal a many-to-many join. If an account appears twice in your lookup table, an ID conversion cannot determine which account snapshot or effective period belongs to the event. Define that relationship separately.

Keep the normalized ID as text. Store the original value, normalized value and validation status in separate fields so the repair is explainable. Retain the source environment as part of the lineage when records from several orgs or test environments enter the same warehouse.

## What should you do when the original 15-character ID has already been lowercased?

Re-extract the ID from the authoritative record or use an independently verified mapping. Once capitalization is lost from a 15-character value, the converter cannot know which original pattern was intended.

Appending a suffix to the damaged string creates a consistently encoded version of the damaged string. It does not recover the missing information. A successful local conversion can therefore make the wrong value look more credible.

For an affected file:

1. Stop the downstream update or import using the damaged identity column.
2. Preserve the original file and the transformation that changed it.
3. Identify another trustworthy reference, such as a governed external key or original export row identifier.
4. Re-extract canonical Salesforce IDs using that reference in the correct environment.
5. Reconcile matched, unmatched and ambiguous rows before rebuilding the destination file.

Do not reconstruct identities from a customer's display name alone. Names can change and need not be unique. If a business key is proposed for recovery, prove its uniqueness for the affected population and document what happens to collisions.

![Preserve the source identity and letter case, recover damaged values, convert or check the canonical ID, verify the target record and quarantine ambiguous relationships before a controlled import.](https://www.martechdevs.com/assets/blog/salesforce-15-vs-18-character-id.svg)

Diagram connections:

- Preserve source ID object and environment to Original letter case available. 
- Original letter case available to Recover identity from authoritative source: No. 
- Original letter case available to Convert or validate canonical 18-character ID: Yes. 
- Recover identity from authoritative source to Convert or validate canonical 18-character ID. 
- Convert or validate canonical 18-character ID to Check target object record and access. 
- Check target object record and access to Exactly one intended relationship. 
- Exactly one intended relationship to Quarantine unmatched or ambiguous rows: No. 
- Exactly one intended relationship to Run a controlled import and reconcile results: Yes

## Why can an 18-character ID still fail in Data Loader?

A correctly formatted ID can still be mapped to the wrong field, reference the wrong object or identify a record outside the intended target context. Use the error and field name to narrow the investigation rather than repeatedly changing the ID length.

| Error or symptom | First check | What conversion cannot establish |
| --- | --- | --- |
| `MALFORMED_ID` | Length, allowed characters, case and field mapping | Whether a text value was incorrectly mapped into a lookup |
| `MISSING_ARGUMENT` for an update | The record-ID column and row mapping | A blank update key cannot identify the target |
| `INVALID_CROSS_REFERENCE_KEY` | Referenced record, target org, lookup type and access | A valid-looking ID need not be an allowed reference |
| Wrong customer updated | Join result and matching field before upload | Successful transport does not prove intended identity |

The first three checks follow Salesforce's [Data Loader troubleshooting](https://help.salesforce.com/s/articleView?id=000387915&language=en_US&type=1). Reproduce the failure with a controlled row and the same integration identity. An administrator's successful lookup is not a substitute for the access the actual job has.

Also distinguish **Id** from relationship fields such as **OwnerId**. Salesforce assigns the record's Id and does not allow updating that field; a reference field holds another record's ID and can have different update behavior. [Object ID and reference fields](https://resources.docs.salesforce.com/latest/latest/en-us/sfdc/pdf/object_reference.pdf). Verify the mapping before changing ownership or associations.

## Is converting IDs enough to prevent duplicate imports?

No. Duplicate prevention depends on the import operation and matching key as well as canonical IDs. Converting an ID does not turn an insert into an update or establish that an external key is unique.

Salesforce upsert can use an ID or a supported matching field. A missing match creates a record, one match updates it, and multiple matches produce an error. [Salesforce upsert behavior](https://trailhead.salesforce.com/content/learn/modules/apex_database/apex_database_dml). Choose that behavior deliberately before running a migration.

For example, a recurring integration might retain its source system's immutable customer key in a governed Salesforce external-ID field. The decision to use that key is separate from the Salesforce record-ID representation. Document which system creates the key, whether it can change, and how existing duplicates are handled.

Before a bulk run, export the target records needed for comparison and preserve a mapping from source identity to target identity. Test a new record, an existing record, a duplicate matching key and a retry. Confirm the intended insert/update counts and inspect rejected rows. Avoid rerunning an entire file as new inserts merely because some updates failed.

## What should pass before you release an ID-normalization change?

The change should preserve known identities, reject damaged inputs and maintain the intended relationships through the actual destination workflow.

## Checklist

- [ ] Original IDs are retained before transformation.
- [ ] The source object and environment are recorded.
- [ ] Both join sides use the agreed canonical representation.
- [ ] The local converter passes known pairs and rejects malformed inputs.
- [ ] Case-collision fixtures remain distinct after normalization.
- [ ] Digit changes are understood to require record-level verification.
- [ ] Unmatched and ambiguous joins remain visible.
- [ ] The actual import identity can access the intended records and fields.
- [ ] Insert, update, retry and rejection counts reconcile.
- [ ] A rollback can restore the prior mapping without guessing customer relationships.

When identity problems span CRM, billing and reporting, our [CRM implementation service](https://www.martechdevs.com/crm-lifecycle) can establish the source keys, matching rules and acceptance checks. Bring a sanitized example of the broken relationship and its expected target. That is more useful than a file of IDs without their business context.

## Frequently asked questions

### Should an 18-character Salesforce ID be shortened to match an old warehouse column?

Prefer widening the warehouse column and normalizing the old data to the agreed canonical format. Shortening every new ID preserves the old limitation and can reintroduce case-sensitive comparison problems. Inspect indexes, joins, exports and downstream schemas before changing the column. Keep the original source value during migration and reconcile relationship counts after the new representation reaches each consumer.

### Can two rows with the same canonical ID still belong in a reporting table?

Yes, when the table intentionally stores snapshots, events or versions for one record. The ID identifies the Salesforce record; it does not identify every analytical row. Add the relevant observation time, event key or validity interval to define the table's grain. A join must select the intended version rather than treating every repeated ID as a duplicate to delete.

### Should a customer-facing application expose Salesforce IDs as its public customer number?

Use a public identifier designed for that application unless there is a clear requirement to expose the CRM identifier. Keep an internal mapping so operations can reconcile records without making the customer experience depend on CRM migration details. An ID is not a credential, but possession of one must never substitute for authorization to read or change the underlying customer record.

### Does a successful ID conversion prove that an Account and Contact are linked correctly?

No. Conversion preserves the representation of each supplied identifier; it does not validate the relationship between them. Check the Contact's intended account reference, the business relationship and the target environment. Include a known incorrect association in your acceptance fixture so the integration proves it can reject a wrong relationship even when both individual IDs have valid shapes.

### How should failed rows be handled when a large ID-conversion batch runs?

Keep a separate exception output containing the source row identifier, original value and precise validation reason. Continue only where the operating policy allows independent rows to proceed. Never silently drop failures or replace missing IDs with placeholders. Reconcile input, accepted and rejected counts, then assign the exception queue to an owner who can recover identities from the authoritative source.

## Sources

1. [Salesforce Object Reference: ID and reference field types](https://resources.docs.salesforce.com/latest/latest/en-us/sfdc/pdf/object_reference.pdf)

2. [Salesforce: Convert a 15-character record ID to 18 characters](https://help.salesforce.com/s/articleView?id=000385066&language=en_US&type=1)

3. [Salesforce: Record-ID report filters and formula columns](https://help.salesforce.com/s/articleView?id=005131264&language=en_US&type=1)

4. [Salesforce: Invalid IDs in Data Loader](https://help.salesforce.com/s/articleView?id=000387915&language=en_US&type=1)

5. [Salesforce Trailhead: Upsert matching behavior](https://trailhead.salesforce.com/content/learn/modules/apex_database/apex_database_dml)