Jump to content
Sign in to follow this  
danielrmitchell

I want to replace text in a field automatically to include hyphens

Recommended Posts

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!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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".

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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 
)

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.