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.

# Excel Query!

3 replies to this topic

### #1 gupta.vijay

gupta.vijay

• Members
• 28 posts
• OFFLINE
•
• Local time:11:25 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?

### #2 Eyesee

Eyesee

Bleepin Teck Shop

• 3,545 posts
• OFFLINE
•
• Gender:Male
• Location:In the middle of Kansas
• Local time:12:55 PM

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:09:55 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:11:25 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