I am inputting Windows Product Keys which contain 25 alphanumeric characters in blocks of 5 characters seperated by hypens " - "

I wish to type the product key without the hypens and have a calculation on the field to add the hyphens every five characters

For example

 

I type 123AB456CD789EF012GH345IJ

FM replaces the text with 123AB-456CD-789EF-012GH-345IJ

 

I have seen this calculation for phone numbers, but does not work directly with the addition of letters

 

Replace ( Replace ( Filter ( Self ; 1234567890 ) ; 7 ; 0 ; "-" ) ; 10 ; 0 ; "-" )

 

The output would be

 

123AB456CD789EF012GH345IJ123456-78-9012345

 

Any help would be great!

I have seen this calculation for phone numbers, but does not work directly with the addition of letters

Replace ( Replace ( Filter ( Self ; 1234567890 ) ; 7 ; 0 ; "-" ) ; 10 ; 0 ; "-" )

 

So remove the Filter() part that leaves only digits and add two more nested Replace()s (since you want to end up with 4 hyphens in total).

 

Note:

IMHO, you should start with =

Substitute ( Self ; "-" ; "" )

Otherwise you will get extraneous hyphens if you try to edit the field.

 

 

 

The output would be

123AB456CD789EF012GH345IJ123456-78-9012345

 

I am afraid that's not clear. I thought the output should be what you said earlier:

123AB-456CD-789EF-012GH-345IJ

Thanks comment, I have this working now thanks to your advice.

 

I have managed what I wanted to achieve using replace but am interested to know why you suggest using substitute instead of replace?

 

Also the output that you are unclear on was caused because I was incorrectly using Upper (Self) & in front of the replace command to make the input all UPPERCASE. so it was changing the input text from lowercase to upper then due to the & was adding the hyphenated text to the end - very confusing, sorry.

 

Here is the calculation I am using now

 

Replace (Replace (Replace ( Replace ( Self ;   6 ; 0 ; "-" ) ; 12 ; 0 ; "-" ) ; 18 ; 0 ; "-") ; 24 ; 0 ; "-")

(which produces the desired hyphens in their correct place)

 

My next problem though is:

 

How do I correctly incorporate the Upper command into the calculation above?

How do I correctly incorporate the Upper command into the calculation above?

 

 Try = 

Replace ( Replace ( Replace ( Replace ( Upper ( Self ) ;  6 ; 0 ; "-" ) ; 12 ; 0 ; "-" ) ; 18 ; 0 ; "-" ) ; 24 ; 0 ; "-" )

 

am interested to know why you suggest using substitute instead of replace?

 

No, not instead, in addition to (or more precisely, before). If you don't, then the first time you enter "1234512345123451234512345" you will get "12345-12345-12345-12345-12345". Then, if you change the first 5 into 6,  you will get "12346--1234-5-123-45-12-345-12345".

Thanks again comment.

I have now realised that I need to learn more about 'nesting' as I am becoming more and more confused.  I can see why the calculation you have provided works, but I cannot for the life of me see how to add the Substitute part which as you have explained would be infinitely useful.

 

Can you recommend a tutorial somewhere that best explains the nesting procedure for a newbie like me?

Nested functions work from the innermost outwards, i.e. each function passes its result “up the chain”; this is clearer if you format the calculation a bit:

Replace ( 
  Replace ( 
    Replace ( 
      Replace ( 
        Upper ( 
          Substitute ( yourInput ; "-" ; "" ) 
        ) ;  
      6 ; 0 ; "-" ) ; 
    12 ; 0 ; "-" ) ; 
  18 ; 0 ; "-" ) ; 
24 ; 0 ; "-" )

You need to know at which stage the result of a given function/calculation is needed; since you always must start with a clean (unhyphenated) version of your text, you need to use Substitute() as the first function, i.e. it is nested the most deeply.

 

OTOH, you can write the calculation in a linear fashion using Let() (and thus avoid nesting), to better see how each result is passed to the next processing stage:

Let ( [
  s = Substitute ( yourInput ; "-" ; "" ) ;
  s = Upper ( s ) ;
  s = Replace ( s ;  6 ; 0 ; "-" ) ; 
  s = Replace ( s ; 12 ; 0 ; "-" ) ; 
  s = Replace ( s ; 18 ; 0 ; "-" ) ; 
  s = Replace ( s ; 24 ; 0 ; "-" )
  ] ;
  s 
)

Thank you eos, that makes a lot sense.

Great help!