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

[Joke]

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

 

[nehpets12]

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

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

 

[Joke]

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]

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]

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]

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]

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]

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.

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]

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]

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.