Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

How to format string as a MAC address

Topic Labels: Formulas
281 3
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

3 Replies 3

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.