Skip to main content

I am storing MAC addresses and we scan a bar code and in inputs the MAC address. The only thing with this is they don’t have the ‘-’. Is it possible to format it to have the - between every 2 characters? Also do I need to have 2 fields for this? The first being the scanned MAC address and the second being a formula? Or can I have a formula and input text then it formats it within the same field? Either way will work.

Hi @JPPSO_NC

This can probably be done with a regular expression much more elegantly that any formula I could come up with.


Yes, you will need a formula field, add this to it. Change Name to what ever field has the wrongly formatted address


LEFT(Name,2)&'-'&MID(Name,3,2)&'-'&MID(Name,5,2)&'-'&MID(Name,7, 2)&'-'&MID(Name,9,2)&'-'&RIGHT(Name,2)

Hi @JPPSO_NC

This can probably be done with a regular expression much more elegantly that any formula I could come up with.


Yes, you will need a formula field, add this to it. Change Name to what ever field has the wrongly formatted address


LEFT(Name,2)&'-'&MID(Name,3,2)&'-'&MID(Name,5,2)&'-'&MID(Name,7, 2)&'-'&MID(Name,9,2)&'-'&RIGHT(Name,2)



This worked perfectly. Thanks.

I tested if this ca be done with REGEX and came up with this:


REGEX_REPLACE(

  REGEX_REPLACE(Name, "(.{2})", "$1-"),

  "-\\z",

  ""

)

^ That adds a - at the end of every two characters, and removes an extra - at the end of the line if present.

Reply


Terms & Conditions