BleepingComputer.com: Excel Query!

I have a excel query:

I have the following type of text in hundreds of rows:

03 Mar 2010 09:49:00 GMT+05:30


I have to delete GMT+05:30 from each row in that cell

What is the easy way for doing this? I can do it by applying a formula in excel but it is of no use to me as it takes the reference of the old cell. Can it be done in any way in excel 2007?

If each string is the same length the LEFT function might do what you want

Here's a VB macro I came up with that might do the trick for you:

Sub del_GMT()

   Dim col As Integer
   Dim LastRow As Integer
   Dim Chars2Keep As Integer
   
   col = 1  'Column A is 1, B is 2, etc...
   Chars2Keep = 20  'will keep this many characters from the left of the string
   
   LastRow = Range("A65536").End(xlUp).Row
    
   For i = 1 To LastRow
        Set curcell = Cells(i, col)
        If curcell.Value <> "" Then
           curcell.Value = Left(curcell.Value, Chars2Keep)
        End If
    Next i
   
End Sub

The assumptions:
Column A contains the cells you want to edit, if not, change the value of the var col to match (A=1, B=2, etc...)

The cell formatting for the cells is Text with Alignment of Left.

The strings are all the same length and format, ex. "03 Mar 2010 09:49:00 GMT+05:30" so that you want to keep the leftmost 20 characters.

If this won't work for you exactly, it should give you enough to go on to come up with something that will work.

Good luck!

This post has been edited by strolln: 09 November 2010 - 01:28 PM

thanks strolln, i will try it and let you know of any issues...