Solved How to format this cell?

[KitaYama]

I'm trying to format a cell to add POS to the first of the typed value and keep the leading zeros.

Input value by user	Result
0125	POS0125
003265	POS003265
326	POS326
00003	POS00003

It means if I type 0125, I want it to be converted to POS0125

I've changed the format of the cell to the following, but none of them gives me the desired result.
"POS" 000000000000
"POS" ###########

Any kind of advice is much appreciated.

 

[arnelgp]

try:

\P\O\S@

 

[cheekybuddha]

PO\S#

or

\P\O\S#

 

[arnelgp]

PO\S#

will remove leading 0?

 

[cheekybuddha]

Can't get it to work with leading zeroes, however.

 

[cheekybuddha]

will remove leading 0?

Yes, see my Post #5, posted at same time you posted.

 

[cheekybuddha]

For me, using @ didn't work - ???

 

[arnelgp]

For me, using @ didn't work - ???

if there is already data, it will not work, but if you re-type, you will see.
you can also Format the cells As Text first before formatting the cells.

 

[KitaYama]

PO\S#

or

\P\O\S#

Seems that Excel doesn't like this format

 

[cheekybuddha]

Nice!

 

[cheekybuddha]

Seems that Excel doesn't like this format

View attachment 102102

Go with Arnel's suggestion.

 

[KitaYama]

Go with Arnel's suggestion.

As I said Excel doesn't accept it.

I select the cell, crl+1, then use custom and paste what @arnelgp suggested.
Excel shows the above error message.

 

[KitaYama]

@arnelgp It seems that I can not make it work.
Can you upload a sample file?

Thanks.

 

[arnelgp]

why the error?

 

[arnelgp]

demo

 

[KitaYama]

why the error?

@arnelgp thanks for the video. But as I said, Excel doesn't accept that format. You can see the image I posted above.
It may have something with reginal settings.

Is there any way to do it in worksheet-OnChange event ? (for the whole C column)

thanks.

 

[KitaYama]

demo

Million thanks.
The format in your demo is PO!S@ (It's different with what you posted in #4)


I wish I had asked sooner. I wasted the whole day on this and couldn't solve the problem.
You did it in less than a minute.
I don't know how to thank you.
Million thanks.

 

[cheekybuddha]

I guess the escape character for your regional variation is ! Instead of \

When you opened Arne's demo Access translated it for you.

 

[KitaYama]

As I had guessed, there's something wrong with reginal settings. Even if I copy-paste @arnelgp's format, Excel doesn't accept it.
I try to do it with a function in worksheet-onchange event.

Thanks to both of you.

 

[cheekybuddha]

Even if I copy-paste @arnelgp's format, Excel doesn't accept it.

Do you need to do it in code?

Surely you just use:
PO!S@