Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Excel Query!


  • Please log in to reply
3 replies to this topic

#1 gupta.vijay

gupta.vijay

  • Members
  • 28 posts
  • OFFLINE
  •  
  • Local time:02:58 PM

Posted 09 November 2010 - 12:31 PM

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?

BC AdBot (Login to Remove)

 


#2 Eyesee

Eyesee

    Bleepin Teck Shop


  • BC Advisor
  • 3,539 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:In the middle of Kansas
  • Local time:04:28 AM

Posted 09 November 2010 - 01:08 PM

If each string is the same length the LEFT function might do what you want
In the beginning there was the command line.

#3 strolln

strolln

  • Members
  • 400 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:N. Calif.
  • Local time:01:28 AM

Posted 09 November 2010 - 01:24 PM

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!

Edited by strolln, 09 November 2010 - 01:28 PM.

To Err is human; to really foul things up requires a Bleeping Computer!

#4 gupta.vijay

gupta.vijay
  • Topic Starter

  • Members
  • 28 posts
  • OFFLINE
  •  
  • Local time:02:58 PM

Posted 09 November 2010 - 04:14 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users