I have an excel with several MAC addresses and I need to add the colon to them. All cells have multiple MACs. For ex. XXXXXXXXXXXX, XXXXXXXXXXXX, XXXXXXXXXXXX. They are split by commas.

Is there a way to do it without splitting the MACs into its own cells?

I have an excel with several MAC addresses and I need to add the colon to them. All cells have multiple MACs. For ex. XXXXXXXXXXXX, XXXXXXXXXXXX, XXXXXXXXXXXX. They are split by commas.

Is there a way to do it without splitting the MACs into its own cells?

• excel
• excel-formula

• 1 Please clearly include some example data and the desired output. – DBS Commented 2 days ago
• What version of Excel do you have? – BigBen Commented 2 days ago

2 Answers 2

Maybe you could try something like this:

=LET(
     a, TEXTSPLIT(E2,", "),
     b, MAP(a, LAMBDA(b, TEXTJOIN(":",,MID(b,SEQUENCE(6)*2-1,2)))),
     ARRAYTOTEXT(b))

• Split the strings using TEXTSPLIT() where the delimiter is a comma,

• Use MAP() function to execute a custom LAMBDA() function to split by every 2 characters and then join again by a colon,

• Finally, using ARRAYTOTEXT() to concatenate the strings into one.

By comms (,) I am assuming they are in rows. If not, refer to Mayukh's answer

You can use TEXTJOIN function, which takes text split using MID function. Since the MAC addresses have fixed size, it would be easier to split function

=TEXTJOIN(":", TRUE, MID(A2, {1,3,5,7,9,11},2))

Here A2 is the source column.