Share via

Disable automatic conversion to number format

Anonymous
2023-02-22T08:35:58+00:00

Hello,

In cells, which are marked as text, if I batch delete text characters, the left numbers automatically converted to numbers and front zeros disappear. If I delete the manually the text characters, they stays as text and front zeros stay in their positions. I have large dbase, cannot do this manually but I need this second option, what should i do?

For example:

Cell contains: 0012SFX

I need: 0012

After batch replacing: 12

Manual deleting SFX: 0012

Thanks for the ideas!

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

3 answers

Sort by: Most helpful
  1. riny 20,870 Reputation points Volunteer Moderator
    2023-02-22T10:11:22+00:00

    Never heard of Zoho Sheets (my bad). You may want to look into Power Query then. Connect to the table with all text strings and split at the point where digits change to non-digits.

    Image

    The end result looks like this

    Image

    Remove the 2nd column if you wish.

    Was this answer helpful?

    0 comments No comments
  2. Anonymous
    2023-02-22T09:55:03+00:00

    Hi,

    Thank you for your reply, I know these methods, unfortunately not working with my database. There are 3-6 numbers with 1-3 zeros in front and 1-4 different characters at the end of it. The amount of character variants is 14. I could finish this task using Zoho Sheets, but it is interesting why excel ingores the format of cells.

    Some examples:

    010C

    021S

    00031S

    0015SFC

    000321TC

    Was this answer helpful?

    0 comments No comments
  3. riny 20,870 Reputation points Volunteer Moderator
    2023-02-22T09:07:26+00:00

    There are many ways to achieve this, but it depends on the consistency of the structure of the text strings. The picture below shows a few examples with formulas in B (as shown in C).

    Image

    The first formula assumes that there are always three characters at the end.

    The second assumes that you always want to keep the first 4 digits.

    The last one assumes that the string always contains SFX at the end.

    And then there could be a dozen more solutions. And there is always text-to-columns or perhaps even Power Query if it gets a bit more complicated.

    If you can give some more realistic examples of how your data looks like, that would be helpful.

    Was this answer helpful?

    0 comments No comments