Calculate times in cne cell with empty line break at the end

Hello,

I'm new here and have some problem with empty line break at the end of a cell.

Calculate.png


I can't used this formula " =RIGHT(A3;5)-LEFT(A3;5) " that I've used in B3 to calculate hours between two times that separate with line break in one cell A3. My problem is the huge data in excel that I've got every month has empty line break at the end of cells, and it was time wasting to delete one by one just to run that formula.

I did tried using FIND & REPLACE to replace line break (CTRl+J) with nothing, its not working and breaking the cells data.

Any idea ?

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

I was misread that announcement before, thanks for reminding me.
Already edit original post with workbook file attachment.

Try this:

=RIGHT(CLEAN(A5),5)-LEFT(CLEAN(A5),5)

Then you must be running a European locale, which you didn't mention.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.