I use excel to correlate key credentials. My keys are in this format xxxx-xx, where 'x' can be a letter or number. Is there any way to format the cells so that I don't have to type the "-" every time? and it will sort properly?
2 Answers
I don't think this is possible directly (see the documentation on formats in Excel).
However, you could use a formula like =CONCATENATE(LEFT(A1,4),"-",RIGHT(A1,2))
to create a formatted column.
Very similar to the above, but in some sense "easier" to change.
WARNING, untested code; it works in open LibreOffice though ;-).
Press ALT-F11
Insert a module, then type this into the editor window:
function alphaFmt(s as string) if len(s)>2 then alphaFmt=left(s,len(s)-2)+"-"+right(s,2) else alphaFmt=s end if end function
Then type:
=alphaFmt(VALUE)
in any cell, replace VALUE by either a literal string, cell reference or formula building a string.