Custom format considering a dash between two numbers

Hey, guys.

Please, is it possible to do something like this?

I have a product code composed by two numbers separated by a dash in a cell (say A1). Example: 34-9.

I want to format that cell in a way it will show this: C0034-E0009.

I've already tried custom format like this "C0000-E0000", but of course it didn't work out.

I know how to do it with formulas and functions, if I separate that number into two cells -- for example: TEXT(A1,"C0000")&"-"&TEXT(B1,"E0000") --, but that's not what I actually need. If I do so, the product code will get changed from 34-9 to C0034-E0009 and this will be a real pain. I don't want to change the code, I just want it to be shown differently. In other words, I will still type 34-9, but it will show C0034-E0009 in the same cell. It's just like dates, I mean, we always type them the right way, but we format the cell so it shows the dates in different ways.

Well, that's it. Is it possible? If I have to use a custom VBA function, created specifically for this purpose, no problem.

Thanks for your attention.

brunces

MrShorty, thanks for your reply.

Well, the point here is this... I have a so called Product code composed by the Client and the Equipment codes, separated by a dash. That's the reason for the C and E (C9999-E9999). This product code was created to make sure the code is unique and that it will never be duplicated. It's been working perfectly well since the beginning. However, we've always tried to follow and use some kind of data standardization/pattern, specially for "good looking purposes". Codes without a pattern make reports very ugly in most of cases.

So, for the time being, our Product code is "ugly" like that, I mean, two distinct numbers separated by a dash, without any pattern or mask. Now, we're trying to change that, however we already have a -- not huge, but very -- big data base with those numbers, and lots of formulas that link them in different spreadsheets. That's why, at first, we want to try to just format what we already have, like a mask, because if we change the code itself, we will have some little problems. For example, the file will definitely get bigger because, imagine, today we store "34-9", I mean, a code with 4 characters; and if we change it, it'll be "C0034-E0009", an 11 characters code for all the records. Big difference. Another thing... Today, when we want to search for a product, we just type "34-9", simple like that. And if we change the code, we will have to type all the 11 characters just to find a record. That will be a real pain.

But... just like you said, it seems there's nothing that can be done regarding text formatting, so we will have to figure out what to do. Maybe we spit the number into two columns and format them separately for searching and reports matters. I don't know. Time for brainstorming now.

Anyway, thank you very much for your attention, MrShorty. I appreciate it.

brunces