Add special characther in notepad /excel-CodePudding

I Need to replace a string like this: 20211201153000

In date format like this

2021/12/01 15:30:00 (yyyy/MM/GG hh:mm:ss)

Any solution?

CodePudding user response：

Another option, using Text function only

1] In B1, enter formula :

=TEXT(A1,"0000\/00\/00\ 00\:00\:00")

or,

2] Convert cell to Date format, in B2 formula become:

=0 TEXT(A2,"0000\/00\/00\ 00\:00\:00")

Custom cell format to: dd/mm/yyyy h:mm:ss

CodePudding user response：

with CONCAT and MID:

=CONCAT(MID(A1,{1,5,7,9,11,13},{4,2,2,2,2,2})&{"/","/"," ",":",":",""})

The above returns a string that looks like a date. To create a true date use the double unary:

=--CONCAT(MID(A1,{1,5,7,9,11,13},{4,2,2,2,2,2})&{"/","/"," ",":",":",""})

And format the output as desired.

CodePudding user response：

Using Notepad :

Ctrl H
Find what: (\d\d\d\d)(\d\d)(\d\d)(\d\d)(\d\d)(\d\d)
Replace with: $1/$2/$3 $4:$5:$6
CHECK Wrap around
CHECK Regular expression
Replace all

Explanation:

(\d\d\d\d)      # group 1, 4 digit. You can use \d{4}. year
(\d\d)          # group 2, 2 digit. month
(\d\d)          # group 3, 2 digit. day
(\d\d)          # group 4, 2 digit. hour
(\d\d)          # group 5, 2 digit. minute
(\d\d)          # group 6, 2 digit. second

Replacement:

$1          # content of group1. year
/           # slash
$2          # content of group2 month
/           # slash
$3          # content of group 3. day
            # a space
$4          # content of group 4. hour
:           # colon
$5          # content of group 5. minute
:           # colon
$6          # content of group 6. second

Screenshot (before):

Screenshot (after):