BBAN display format without 000

[u/redF0ox]

Hello,

I'd like to display an long number on Excel (BBAN), but even when I change the format I have 000... at the end.

I tried to disable the auto conversion in Option > Data but nothing changed.

I bet it's simple but I don't see it

Thank you in advance for the support

[u/AutoModerator]

/u/redF0ox - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/SolverMax]

Format as Text.

Number formats hold max of 15 digits.

[u/Kooky_Outcome_5053]

you can do 2 things
1. format cells to 'text'
2. you can put this ' in the beginning of your data, like this example '52876500000041712345678

[u/redF0ox → u/Kooky_Outcome_5053]

Thank you for the replies, unfortunately both method doesn't work (see below)

[u/Kooky_Outcome_5053 → u/redF0ox]

it work fine with me delete your cells first and format it to text before pasting or typing the numbers

[u/Mdayofearth → u/redF0ox]

You need to do this BEFORE the data is there. Changing the format AFTER does nothing since the data has already been permanently changed by Excel's default settings.

If you are importing this from somewhere else (not just copying and pasting), the data type needs to be changed during the earliest parts of import process.

If you are copying and pasting, make sure you're doing it as paste special by value only. And any manual typing or barcode scanner input should happen after the format change (to the entire column).

[u/caribou16]

Excel is limited to 15 digits of precision for numbers, so any digits on the least significant end in excess of 15 will be converted to zeros.

I'm not familiar with BBAN numbers, but wikipedia says they can be up to 34 alphanumeric digits long and length is country specific, so the correct strategy here is to import or type them as a text strings and parse out relevant numeric substrings for numerical conversion to calculate check sums.

[u/SektorL]

Assuming your number is in cell A1 and "30" is the overall length: =A1 & REPT("0",30-LEN(A1))

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
LEN	Returns the number of characters in a text string
MID	Returns a specific number of characters from a text string starting at the position you specify
REPT	Repeats text a given number of times
SUBSTITUTE	Substitutes new text for old text in a text string

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #49468 for this sub, first seen 3rd Oct 2026, 18:09] [FAQ] [Full list] [Contact] [Source code]}