Splitting large sting of data

[DPChristman]

I have a large string of data (25 characters), that basically consists of
5 characters for the location number
2 characters for the terminal number
4 characters for the transaction number
8 digits for the date
6 digits for the time
I basically want to separate the date into groups using special characters.
Example:

1234501123420240214144322 Would become 12345-01-1234-2024/02/14-14:4322

I know there is a way to do this, but I cannot think of it.

Any ideas?

 

[dreid1011]

Maybe this to get you started:

Book1
	A	B
1	1234501123420240214144322	12345-01-1234-2024/02/14-14:4322
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TEXTJOIN("-",,LEFT(A1,5),MID(A1,6,2),MID(A1,8,4),TEXTJOIN("/",,MID(A1,12,4),MID(A1,16,2),MID(A1,18,2)),MID(A1,20,2))&":"&RIGHT(A1,4)

 

[DPChristman]

Perfect, Thanks

Quick quest - What do I need to include to get the end (14:4322) to display as 14:43:22, or to remove the 22 completely.

Thanks again

 

[dreid1011]

Perfect, Thanks

Quick quest - What do I need to include to get the end (14:4322) to display as 14:43:22, or to remove the 22 completely.

Thanks again

Book1
	A	B
1	1234501123420240214144322	12345-01-1234-2024/02/14-14:43
2		12345-01-1234-2024/02/14-14:43:22
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TEXTJOIN("-",,LEFT(A1,5),MID(A1,6,2),MID(A1,8,4),TEXTJOIN("/",,MID(A1,12,4),MID(A1,16,2),MID(A1,18,2)),MID(A1,20,2))&":"&LEFT(RIGHT(A1,4),2)
B2	B2	=TEXTJOIN("-",,LEFT(A1,5),MID(A1,6,2),MID(A1,8,4),TEXTJOIN("/",,MID(A1,12,4),MID(A1,16,2),MID(A1,18,2)),MID(A1,20,2))&":"&LEFT(RIGHT(A1,4),2)&":"&RIGHT(A1,2)

 

[dreid1011]

Perfect, Thanks

Quick quest - What do I need to include to get the end (14:4322) to display as 14:43:22, or to remove the 22 completely.

Thanks again

You're welcome, and happy to help.

 

[dreid1011]

Another option which may be easier to read:

=TEXT(LEFT(A1,11),"#####-##-####-")&TEXT(MID(A1,12,8),"####\/##\/##-")&TEXT(RIGHT(A1,6),"##\:##\:##")

 

[DPChristman]

Book1
	A	B
1	1234501123420240214144322	12345-01-1234-2024/02/14-14:43
2		12345-01-1234-2024/02/14-14:43:22
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TEXTJOIN("-",,LEFT(A1,5),MID(A1,6,2),MID(A1,8,4),TEXTJOIN("/",,MID(A1,12,4),MID(A1,16,2),MID(A1,18,2)),MID(A1,20,2))&":"&LEFT(RIGHT(A1,4),2)
B2	B2	=TEXTJOIN("-",,LEFT(A1,5),MID(A1,6,2),MID(A1,8,4),TEXTJOIN("/",,MID(A1,12,4),MID(A1,16,2),MID(A1,18,2)),MID(A1,20,2))&":"&LEFT(RIGHT(A1,4),2)&":"&RIGHT(A1,2)

Thanks

 

[DPChristman]

=TEXT(LEFT(A1,11),"#####-##-####-")&TEXT(MID(A1,12,8),"####\/##\/##-")&TEXT(RIGHT(A1,6),"##\:##\:##")

Yeah that works perfectly. Thanks!

 

[Peter_SSs]

Yeah that works perfectly. Thanks!

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers.

BTW, you could do it in 2 parts rather than 3 by combining the date & time parts together
Also, by using "0" rather than "#" as the place-holders, if it is possible that a "location number" could have one or more leading zeros, this formula would retain those zeros whereas the earlier formula would drop them leaving you with a less than 5 character location number. See row 2 below.

24 02 17.xlsm
	A	B	C
1		Peter	dreid
2	1234501123420240214144322	12345-01-1234-2024/02/14-14:43:22	12345-01-1234-2024/02/14-14:43:22
3	0004501123420240214144322	00045-01-1234-2024/02/14-14:43:22	45-01-1234-2024/02/14-14:43:22
Reformat

Cell Formulas
Range	Formula
B2:B3	B2	=TEXT(LEFT(A2,11),"00000-00-0000-")&TEXT(RIGHT(A2,14),"0000\/00\/00-00\:00\:00")
C2:C3	C2	=TEXT(LEFT(A2,11),"#####-##-####-")&TEXT(MID(A2,12,8),"####\/##\/##-")&TEXT(RIGHT(A2,6),"##\:##\:##")