Change Date Format

Hello there,

is there any chance to change a date like: 26 Mar 2016 to the actual date, I need to have it as values (numbers or date)..

Also I have a problem with data that I extracted and one cell is always adding an additional space at the end. I tried Trim(text) already but not working at all.


thanks for help,

Try this:

Formula:

Please Login or Register  to view this content.

Hey thanks for quick response,

its also not working, it think there is some problem with the inserted data, well there is also a space at the end.

see attached excel maybe you can fix it in there ...

Hmm... you are right. That additional space at the end is weird. This should fix it:

Formula:

Please Login or Register  to view this content.

Hmm... you are right. That additional space at the end is weird. This should fix it:

Formula:

Please Login or Register  to view this content.

Great thank you so much for help, it works perfectly!!

Well just to understand for myself, I knew the trick with the TRIM function, but how would you do it if you only want to get rid of the space at the end for other cells that have the same problem?

That space at the end is actually the non-breaking space character, with a code of 160 rather than a normal space which has a code of 32. You can remove them all in a simple operation using Find & Replace.

Select all your cells by doing CTRL-A or clicking on the intersection between the row and column identifiers.
CTRL-H will bring up the F&R dialogue box:

FIND What: ALT-0160
Replace with: leave blank
Click Replace All

Where ALT-0160 means you should hold down the Alt key and type 0160 on the numeric keypad (or you could just copy one of those "spaces" into the box).

Hope this helps.

Pete

Originally Posted by furstone23

26 Mar 2016 to the actual date
thanks for help,

or try this... =--SUBSTITUTE(TRIM(SUBSTITUTE(A4,CHAR(160),""))," ","-")

Thank you very much guys, I fixed the issue.

Thanks @Pete_UK this is the easiest way and it even transforms the date into its normal values. Cheers man!

You're welcome.

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

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete