Excel 2010 – date field leading space and serial value problems

Topic

New Reply

Due to a bug in Crystal Reports, date fields export into Excel with a leading space in the cell.

As on this occasion VBA is not any option, I attempted to remove the leading space with a formula that uses substitute, as follows:

A1 = leading space + date (format yyyy-mm-dd)
B1 = substitute (A1,” “,””)

This removed the space, but displayed the field as a serial value, instead of the date in A1.

I then tried:

B1 = value(substitute(A1,” “,””))

This formatted the field as a date, but left the leading space in the field.

I would be very grateful for some help with this.

useful

Reply | Quote

Replies

Hi

This should do it:
B1=TEXT(TRIM(A1),”yyyy-mm-dd”)

zeddy

Reply | Quote

Hi Zeddy

What can I say … you are a real genius! I am so grateful that you responded and did it so promptly!

I only wish I could return the favour!

Eternally grateful!

useful

Reply | Quote