Covert the word

[anee_4285]

Hello Team,

I want to covert 00:50:56:A4:19:C0 into 0050.56A4.19C0. Can we do it in excel? i have the big list in the Row A

 

[inactiveuserps07]

@anee_4285, if your list begins in A2, then use this formula in B2:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,":","",1),":",".",1),":","",1),":",".",1),":","",1)

... and drag-copy down as far as you need.

 

[inactiveuserps07]

This would do the same thing:

=LEFT(SUBSTITUTE(A2,":",""),4)&"."&MID(SUBSTITUTE(A2,":",""),5,4)&"."&RIGHT(SUBSTITUTE(A2,":",""),4)

 

[Tetra201]

A shorter one:

=REPLACE(REPLACE(SUBSTITUTE(A2,":",""),9,0,"."),5,0,".")

 

[inactiveuserps07]

@Tetra201, I always seem to forget that REPLACE() will work with 0. Thanks for the reminder.

 

[anee_4285]

@anee_4285, if your list begins in A2, then use this formula in B2:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,":","",1),":",".",1),":","",1),":",".",1),":","",1)

... and drag-copy down as far as you need.

Hello ErikTyler ,

Its working. Thanks.

 

[inactiveuserps07]

Great, Anil. You may also want to take a look at Tetra201's formula from post #4 above. They all do the same thing, and if your list isn't 100,000 rows long, it probably won't make a difference in speed at all. But it's good to know different approaches.