Disable automatic conversion to number format

Question

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!

Answer 1

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.

The end result looks like this

Remove the 2nd column if you wish.

Answer 2

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

Answer 3

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

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.