Someone has sent me a huge spreadsheet with a column full of dates. Well,
not really, each date has a blank space in front of it, so I guess even
though it may say 02/23/2004, it is really just a label.

I need to have the dates so I can subtract them from one another, etc.

I would like to have a macro that can go down the column (say start in cell
c5 and go down as far as c4000 and remove the space in front of each entry.
Can someone tell me how to write that macro?

Thanks much!
Dean

Hi Dean,

No need for macro I guess. Just select the column (range) which contains the
"dates", format it as date (just in case), go to menu EditReplace, in the
'Find what' box type a space, leave the 'Replace with' box blank, press
'Replace All'

Regards,
KL


"Dean" wrote in message
...
Someone has sent me a huge spreadsheet with a column full of dates. Well,
not really, each date has a blank space in front of it, so I guess even
though it may say 02/23/2004, it is really just a label.

I need to have the dates so I can subtract them from one another, etc.

I would like to have a macro that can go down the column (say start in
cell c5 and go down as far as c4000 and remove the space in front of each
entry. Can someone tell me how to write that macro?

Thanks much!
Dean

hi,
you could use this macro...it assumes that the blank space is after the
date...if it is before, you could use the right(..) function instead of the
left(..) function.

Sub correctDate()
Dim range_cell As Range
For Each range_cell In ActiveSheet.Range("c15:c4000")
range_cell.Value = Left(range_cell.Value, Len(range_cell.Value) - 1)
Next range_cell
End Sub

hope it helps

MPG

"Dean" wrote in message
...
Someone has sent me a huge spreadsheet with a column full of dates. Well,
not really, each date has a blank space in front of it, so I guess even
though it may say 02/23/2004, it is really just a label.

I need to have the dates so I can subtract them from one another, etc.

I would like to have a macro that can go down the column (say start in
cell c5 and go down as far as c4000 and remove the space in front of each
entry. Can someone tell me how to write that macro?

Thanks much!
Dean

Hi Dean,
You don't need a macro! Supposing your dates with leading spaces are in
column A, just put a =trim(A1) function in a new column formatted to a
desired date format, and fill it down to the last row as necessary.
Regards,
Stefi


€žDean€� ezt Ã*rta:

Someone has sent me a huge spreadsheet with a column full of dates. Well,
not really, each date has a blank space in front of it, so I guess even
though it may say 02/23/2004, it is really just a label.

I need to have the dates so I can subtract them from one another, etc.

I would like to have a macro that can go down the column (say start in cell
c5 and go down as far as c4000 and remove the space in front of each entry.
Can someone tell me how to write that macro?

Thanks much!
Dean

you could also do this:
select the entire column
click data and then text to columns
choose delimited
click next
put a check next to space under delimiters
click next and then finish

it will put the dates in the next column to the right. if you have data
there now, just insert a column to the right of the original "date" field

--


Gary


"Dean" wrote in message
...
Someone has sent me a huge spreadsheet with a column full of dates. Well,
not really, each date has a blank space in front of it, so I guess even
though it may say 02/23/2004, it is really just a label.

I need to have the dates so I can subtract them from one another, etc.

I would like to have a macro that can go down the column (say start in
cell c5 and go down as far as c4000 and remove the space in front of each
entry. Can someone tell me how to write that macro?

Thanks much!
Dean

Clever! This is the best solution, it makes the trick in situ without using
an extra column!
Stefi

€žKL€� ezt Ã*rta:

Hi Dean,

No need for macro I guess. Just select the column (range) which contains the
"dates", format it as date (just in case), go to menu EditReplace, in the
'Find what' box type a space, leave the 'Replace with' box blank, press
'Replace All'

Regards,
KL


"Dean" wrote in message
...
Someone has sent me a huge spreadsheet with a column full of dates. Well,
not really, each date has a blank space in front of it, so I guess even
though it may say 02/23/2004, it is really just a label.

I need to have the dates so I can subtract them from one another, etc.

I would like to have a macro that can go down the column (say start in
cell c5 and go down as far as c4000 and remove the space in front of each
entry. Can someone tell me how to write that macro?

Thanks much!
Dean