How to format string as a MAC address

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)

image

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.

1 Like