how to use custom format to enter nr&symbols in a cell

Joke

Board Regular
Joined
Jan 21, 2004
Messages
171
I'm battling with the custom format for cells

I'm trying to get a fixed format to enter national identity numbers like for example:


001-050379-2025J

I can't get this cell format setting to work .....
Any suggestions??

Joke
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

nehpets12

Active Member
Joined
Feb 22, 2002
Messages
453
Re: how to use custom format to enter nr&symbols in a ce

Try Format Cells Custom and type 000-000000-0000J
 

Joke

Board Regular
Joined
Jan 21, 2004
Messages
171
Re: how to use custom format to enter nr&symbols in a ce

It doesnot work XLS doesnot do anything to the format. I would like XLS to automatically put the - in between the numbers that you enter. The letter at the end changes for every identitynr.

any other ideas?

thanks again,

Joke
 

mgana

Board Regular
Joined
Jul 18, 2003
Messages
61
Re: how to use custom format to enter nr&symbols in a ce

Try Format Cells Custom and type 000"-"000000"-"0000J

When entering the numbers in the cells, just go ahead and finish typing. As you exit the cell you will see it in the desired format.
 

Joke

Board Regular
Joined
Jan 21, 2004
Messages
171

ADVERTISEMENT

Re: how to use custom format to enter nr&symbols in a ce

It is getting clearer here is what happens:

if I use your format and enter only the 13 numbers it comes out fine with the - in between the numbers and the J at the end.

only that the J is not standard every identity number has another letter at the end. If I enter the Number including the J then it doesnot take the format anymore..........

so the question is how to include in the format one position for a letter at the end ???

Thanks again,
Joke
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Re: how to use custom format to enter nr&symbols in a ce

Ideally you want something like 000-000000-0000@ but Excel baulks at this as it doesnt like the mixture of variable text and numbers. It seems the only way you may be able to achieve this is with a worksheet_change event to reformat the cell.
 

Joke

Board Regular
Joined
Jan 21, 2004
Messages
171

ADVERTISEMENT

Re: how to use custom format to enter nr&symbols in a ce

life is never simple do you mean an opening macro....... I'm still at basic level of macros any idea how it should look like?

Joke
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Re: how to use custom format to enter nr&symbols in a ce

Hi, heres an example. I have limited the change to affect only cells changed in column A as you should limit the macro to run only where you will have this format. Just change this to whatever column number you will be entering this format. (Col B = 2, Col C = 3 etc)
eg for column C change to...
If Target.Column <> 3 Then Exit Sub

This macro works by first taking all the characters from the left except the last one and putting it into a variable NewVal in the shortened format of "000-000000-0000" as we know the numbers get formatted correctly. The last character then gets added to the string to complete the format.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewVal As String

'Only make changes to cells in column A
If Target.Column <> 1 Then Exit Sub

'Only make changes if it isnt already formatted
If InStr(1, Target.Value, "-", vbTextCompare) > 0 Then Exit Sub

'Put target value minus 1 character into the new format
NewVal = Format(Left(Target.Value, Len(Target.Value) - 1), _
"000-000000-0000")

'Add last character to the format
NewVal = NewVal & Right(Target.Value, 1)

'Put format into cell
Target.Value = NewVal

End Sub

Note that you enter numbers like 10503792025J and this gets formatted to 001-050379-2025J. If you enter text in any part except the last character it will result in just as you typed and wont be reformatted with hyphens.
eg test123 = test123

EDIT: I should add where you place the macro. Right click the sheet where you will have this format and select the option View Code. Paste the code into the big white area on the right.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
As an aside, it should come as no surprise that the custom number format feature does not support formats for aplhanumeric (ie text) strings :)
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Re: how to use custom format to enter nr&symbols in a ce

Hi Paddy, hope your enjoying the sunshine in Wellington while it lasts. :)

I admit I was a bit surprised it didnt like 000@ as it does accept 000J for example. However while this looks alpha-numeric Excel still treats it as a number like you say. Its interesting to note that certain letters can be added but not others - try putting in 000M or 000B. Presumably thats because M is used in the date format but Im not sure about B.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,097
Messages
5,768,071
Members
425,452
Latest member
htay44

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top