0

I have a column with a bunch of two 4 digits.

  1. 1354 1356
  2. 1402 1421
  3. 1431 1452
  4. 1532 1546 ... and so on

I've been trying to convert these to look more like

  1. 13:54 ~ 13:56
  2. 14:02 ~ 14:21
  3. 14:31 ~ 14:52
  4. 15:32 ~ 15:46 ...

I've tried wildcard but it's too simple to handle that. I couldn't find a function nor was I successful with date/time format. I'm not sure if I should learn VBA for that.

Improve this question
2
  • 1
    Use MID() function and concatenation.
    – Akina
    Commented Oct 18, 2019 at 12:08
  • 2
    have a try and share us what you get.. We'll help you till you get it.. /(^_^)
    – p._phidot_
    Commented Oct 18, 2019 at 12:18

2 Answers 2

Reset to default
2

Use Nested REPLACE(), Note start from the right:

=REPLACE(REPLACE(REPLACE(A1,8,0,":"),5,1," ~ "),3,0,":")

enter image description here

Improve this answer
2

A combination of CONCATENATE and MID will help. Of course this is a text function so end results are Text.

enter image description here

Improve this answer
1
  • As a small suggestion, perhaps consider adding a text version of the command to your answer. It might be a minor inconvenience, but copying and pasting is often simpler than trying to type a long command out from a screenshot with two separate windows open.
    – Anaksunaman
    Commented Oct 18, 2019 at 20:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .