• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find & Replace on two spaces not working

D

DashboardNovice

Member
In the attached file, there are two space characters after the year and before the time. I did Find & Replace and it said 101 replacements made, but I don't see any difference.
 

Attachments

  • Chandoo.org - Double Spaces Not Recognized.xlsx
    9.7 KB · Views: 2
its a date and formatted cell - so actually in the cell is a number - so NO spaces really in the display
its mask that excel uses to display date and time from a number , time is the decimal part, and day is the number of days since JAN 1900
change to general format and you will see the numbers

you could use a customer format to remove the space if wanted to
dd/mm/yyyyhh:mm
 

Attachments

  • Chandoo.org - Double Spaces Not Recognized-etaf.xlsx
    9.5 KB · Views: 1
Last edited:
ETAF said:
its a date and formatted cell - so actually in the cell is a number - so NO spaces really in the display
its mask that excel uses to display date and time from a number , time is the decimal part, and day is the number of days since JAN 1900
change to general format and you will see the numbers

you could use a customer format to remove the space if wanted to
dd/mm/yyyyhh:mm
Click to expand...

I tried custom data format and it changed how it appear on the surface, but the two consecutive spaces are still there and it's cause my graph (I should have mentioned this initially) to look really weird. So to get my graph to look right, I still have to remove those two spaces and replace with 1, but Find and Replace isn't doing that for some reason.
 
as mentioned find and replace is not going to do anything as its a date and that means its a number in the cell, as mentioned

maybe for the graph you could change the date into a TEXT format (wont work as a date in any calculation or sorting

=TEXT(E4,"dd/mm/yyyy hh:mm")
see column G
then i copied and paste special into column I

perhaps show a sample with the graph and how you want it to show , and what the problem is with the format
 

Attachments

  • Chandoo.org - Double Spaces Not Recognized-etaf1.xlsx
    13 KB · Views: 2
ETAF said:
as mentioned find and replace is not going to do anything as its a date and that means its a number in the cell, as mentioned

maybe for the graph you could change the date into a TEXT format (wont work as a date in any calculation or sorting

=TEXT(E4,"dd/mm/yyyy hh:mm")
see column G
then i copied and paste special into column I

perhaps show a sample with the graph and how you want it to show , and what the problem is with the format
Click to expand...
That makes sense. Thank you.
 
You must log in or register to reply here.
Back
Top