Need to break a text/number sequence and insert :

Anyone know of a quick (formula) way to take an entry like...
0040580D007A

and insert colons after every 2 chars?
00:40:58:0D:00:7A

I know I could use LEFT/MID/RIGHT etc, but cant think of another way right niow

You might be able to do it quickly in PowerQuery.

M code:

Please Login or Register  to view this content.

Ali, thanks for the suggestions

It's for something my wife has to do at work, they dont have that feature, and she is not that advanced in excel anyway. Macros are kinda out as well

She has 2 files, 1 with the IP of all the clock/punch machines in the facility, along with their locations (they contain the colons), and another file that contains what clock people punched in on, these dont contain the columns and actually have some extraneous trailing data that needs to be removed. She gets a new copy of the 2nd file every day, and need to ID those ppl that did not punch in on the correct clock

What Im trying to get for her, is some formula that she can paste into the 2nd file, copy it down, and it will then show what she needs.

I will be using a simple vlookup into the 1st file, and as I said, I could brute-force a LEFT/MID/RIGHT to insert colons, but Im sure there is a simpler, more elegant option

Mmmm. Not as easy as it should be, is it? I can't think of a way in a simple formula, unless you use the REPT function. Alkey will be your man for this!

Yes, exactly lol. I know I can do it, but it will be messy

OK this is the mess I came up with so far...
=VLOOKUP(LEFT(AD18,2)&":"&MID(AD18,3,2)&":"&MID(AD18,5,2)&":"&MID(AD18,7,2)&":"&MID(AD18,9,2)&":"&MID(AD18,11,2),'[file-name-removed-to-protect-the-innocent,xlsx]sheet1'!$B$5:$C$93,2,0)

AD18=0040580D007A (134325)
I convert to this...
00:40:58:0D:00:7A
LEFT(AD18,2)&":"&MID(AD18,3,2)&":"&MID(AD18,5,2)&":"&MID(AD18,7,2)&":"&MID(AD18,9,2)&":"&MID(AD18,11,2)

...yucky

On a side note, I have committed the same error in posting this, as many other OP's do - not giving enough detail or background on what I need and why I need it a certain way

...Hey guys, convert 0040580D007A to 00:40:58:0D:00:7A for me please...
lol

I did think that, Ford, but didn't dare say so ...

Been there, done that!

What about a blank cell to the right. Then this formula that progressively concatenates to the right. In C1 and across.

Formula:

Please Login or Register  to view this content.

	A	B	C	D	E	F	G	H
1	0040580D007A		00:	00:40:	00:40:58:	00:40:58:0D:	00:40:58:0D:00:	00:40:58:0D:00:7A

Mr.Ford

I have a solution too but with helper column.

C2=IFERROR(INDEX(MID($A2,ROW(INDIRECT("1:"&LEN($A2))),2)&":",(COLUMNS($C$2:C2)-1)*2+1),"")


Copy towards the cells.

B2=SUBSTITUTE(C2&D2&E2&F2&G2&H2&I2,":","",LEN(C2&D2&E2&F2&G2&H2&I2)-LEN(SUBSTITUTE(C2&D2&E2&F2&G2&H2&I2,":","")))

Row\Col	A	B	C	D	E	F	G	H	I
1	Data	Result	Helper Column	Helper Column	Helper Column	Helper Column	Helper Column	Helper Column	Helper Column
2	0040580D007A	00:40:58:0D:00:7A	00:	40:	58:	0D:	00:	7A:

Dave, thanks for that, and yes, that would be an option, and I have also used that approach before, too

However, Im trying to keep this as simple as I can, with as few steps as possible

shukla.ankur281190 thanks a ton for the suggestion, but the same comments apply as was given to Dave

Then there is always brute force. (Scuri won't let me play without spaces in the formula. )

=REP LACE(REP LACE(REP LACE(REP LACE(REP LACE(A2,11,0,":"),9,0,":"),7,0,":"),5,0,":"),3,0,":")

Woww Flame love this replace formula simply just woww. Hats-off Dave

Originally Posted by FlameRetired

Then there is always brute force. (Scuri won't let me play without spaces in the formula. )

=REP LACE(REP LACE(REP LACE(REP LACE(REP LACE(A2,11,0,":"),9,0,":"),7,0,":"),5,0,":"),3,0,":")

Thats shorter than my LEFT/MID version, thanks

Ford I just tried Flash fill and it seems to work well.

testing no spaces
...
OK didnt work for me eitrher

Originally Posted by FlameRetired

Ford I just tried Flash fill and it seems to work well.

enlighten me please?
(my excuse, it's late and my bed is calling...3:05 am)

Using the zero value never occurred to me - brilliant, Dave!!!

There isnt always a zero to latch onto in each couple.
Re-looking at her locations, the 1st 3 couples (6 values) are all the same, the 1st 4 all start with 0, but after that, looks like it can vary
00:40:58:0D:03:82
00:40:58:0C:E8:EB
00:40:58:0C:FF:2C
00:40:58:OC:FF:27
00:40:58:0C:E8:EC
00:40:58:0C:E8:01
00:40:58:0C:E8:24

Just type a single example of desired output in B1. Then DATA > Flash Fill

Scuri won't let me post the Grid shot. I'll try an upload if this doesn't post.

oh ok DUH lol

Another wrinkle is the trailing junk
0040580CEE5B (134327)
0040580CEE5B (134327)
0040580D142E (134318)
0040580D142E (134318)
0040580CE801 (134305)
0040580CE9E3 (134315)
0040580CE9E3 (134315)
0040580D1200 (134310)
0040580CE8EB (134301)
0040580CEE5B (134327)
0040580CEE5B (134327)
but that's manageable, too

Originally Posted by FDibbins

There isnt always a zero to latch onto in each couple.
Re-looking at her locations, the 1st 3 couples (6 values) are all the same, the 1st 4 all start with 0, but after that, looks like it can vary
00:40:58:0D:03:82
00:40:58:0C:E8:EB
00:40:58:0C:FF:2C
00:40:58:OC:FF:27
00:40:58:0C:E8:EC
00:40:58:0C:E8:01
00:40:58:0C:E8:24

I don't understand what you mean.

Anyway here's the upload to prove I might actually not be crazy. (Scuri ... Grrr!)

Sorry, post 20 was showing what the location IP's looked like (what she needs to search though) - file 1
post 22 was what her daily data looks like - she needs to search file 1 to find these entries - file 2

I can hear my pillow calling my name. I will pick this up again tomorrow (later today)

Thanks again to all who have offered suggestions

Sweet dreams, Ford!

Sleep tight. I'm on my way, too.

Ford you mentioned what your wife does not have access to PowerQuery so I am guessing this is out of reach as well (Office 365 subscription).

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

I don't have 365 so I can't test. I also don't know if it has to be array entered. It seemed worth mentioning just in case.

Just tried it, Dave, and it does work perfectly - no need for CSE.

Dave, you are correct

She works for the state (PA), and they are very fussy about that sort of thing. (I worked there too, until Dec last year, had to quit because things became untenable)

Just FYI for other members, TEXTJOIN (among a few others) is also not available in 2016 desktop

Originally Posted by AliGW

Just tried it, Dave, and it does work perfectly - no need for CSE.

Thank you for that Ali. I occasionally suggest TEXTJOIN in posts. It's good to know about CSE for certain now.

On a side note, I said these where IP addy's, they are actually MAC addys

It's a shame - there are a few really handy functions that only exist in 365.

Originally Posted by AliGW

It's a shame - there are a few really handy functions that only exist in 365.

Agreed, I think some are also available for MAC, and (maybe) office pro

Originally Posted by FDibbins

Just FYI for other members, TEXTJOIN (among a few others) is also not available in 2016 desktop

Just to be VERY clear: it DOES exist in 2016 desktop IF you have a 365 subscription!!! In other words, it's not just available in the browser version.

Ford that's too bad. It was a long shot that sounded like the perfect solution.

Meanwhile I kept busy "googling" file formats and looking for some hidden little known setting in HEX2DEC/DEC2HEX or better yet some TEXT string format that is not commonly reported like the R1C1 string "R0C00000". The information is overwhelming. None of it usable. (Whoever heard of Delta V time format?).

Delta V time format - the Vogons, maybe???

or Vulcans?

It's probably the Vogons' poetic version of iambic pentameter.

About the SKU differences in 2016: That one really *@#0's me off. It's one thing to not include features like Power Pivot ... I understand that ... but to withhold FUNCTIONS is inexcusable. It will be interesting to see what happens in the 2019/2022 versions.

Originally Posted by AliGW

Delta V time format - the Vogons, maybe???

Originally Posted by FDibbins

or Vulcans?

Originally Posted by AliGW

It's probably the Vogons' poetic version of iambic pentameter.

Delta V rocket time?

iambic pentameter ... I like it. With or without couplets?

Dave - surely you realise that it must have quintuplets?

HA! Good one! Deserves rep.

Thanks, Dave - very kind!!! Love a bit of wordplay - after all, words are my day job.