Formatting in Bulk

[Graham C1600]

Hi,

Is there a simple way to change the following in bulk ?

11/04/2022 01:00:00

I need it to look like this

11/04/2022 01:00:00

I know I can Alt+Ent in singular cells but I have to do around 200 cells at a time.
If I check the formatting of the cells it is Custom dd/mm/yyyy hh:mm

I'm copying data from another source and pasting into excel and was wondering if there was an easier way of doing this.

Thanks

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try this.
Select the data/column etc and in the Custom formatting dialog, remove that space character between y and h and type Ctrl+J there instead. Set the cell formatting to Wrap Text

 

[Graham C1600]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try this.
Select the data/column etc and in the Custom formatting dialog, remove that space character between y and h and type Ctrl+J there instead. Set the cell formatting to Wrap Text

Worked a treat. Thank you very much.

 

[kvsrinivasamurthy]

Data Range "G2:G6"
Sub Macro()
Dim K As Double
For Each Cel In Range("G2:G6")
K = Cel
Cel.Value = Format(Int(K), "dd:mm:YY") & Chr(10) & Format(K - Int(K), "hh:mm:ss")
Next Cel
End sub

 

[Peter_SSs]

Worked a treat. Thank you very much.

You're welcome. Thanks for the follow-up.

.. and thanks for updating your details.

@kvsrinivasamurthy

• When posting vba code in the forum, please use the available code tags. My signature block below has more details.
  
  
• Your suggestion would turn the cell values from actual date/time values (numerical) to text values which would deny any future date/time calculations from those values.

 

[kvsrinivasamurthy]

You're welcome. Thanks for the follow-up.

.. and thanks for updating your details.

@kvsrinivasamurthy

• When posting vba code in the forum, please use the available code tags. My signature block below has more details.
  
  
• Your suggestion would turn the cell values from actual date/time values (numerical) to text values which would deny any future date/time calculations from those values.

Sorry for forgetting to put codes in tags. I will take care in future. Happy if problem is solved.