2

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?

rob
  • 21

2 Answers2

3

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.

D Schlachter
  • 2,058
1

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.

Hannu
  • 10,568