delete part of cell's content

[afgi]

Hi,

I would like to make a makro that delate part of text fro a range of cells. so sample text is "thu 29 jul 2010 17:50:07 CEST" and I want to have only time "17:50:07". each cell has different details, but the same order. moreover each one has the word "CEST" at the end. any ideas??? many thanks for help. an example is in attachment

[DonkeyOte]

If you want to use VBA then one approach might be:

Please Login or Register  to view this content.

[afgi]

hi,

I am getting run time error 424 on line "With .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))"

[peterjuhnke]

if you can use a formula, will this do?
regards
peter

[afgi]

hi,

I am sorry, but I have no idea what formula you are talkin about. the thing you did in the excel table is what I want, but I do not know how to make it by my self... can you write it step by step. sorry for trouble and thanks

[afgi]

Dear Peter,

and one more thing. the cell contains date and time. so time is "17:50:07 " and date "thu 29 jul 2010". are you able to make makro that will extract these two details from the text in each cell.
so:
column A - the sentence as in first post
column B - date, that is 29/07/2010. it takes number 29 as a day; jul as a month and converse it to a number, one of 12; 2010 is a year
column C - time, f.e. 17:50:07
it would be very useful, if columns B & C can be read by excel as a accordingly: date and time


many thanks!!

[peterjuhnke]

hello afgi
I see that czw is in each cell of the example, is this constant on the real spread sheet?
I assume 29 lip 2010 is 29 jul 2010.
Can both time and date be in the same cell or in two seperate cells?
I tried to show the brake down in the example, the SUBSTITUTE formula (col B) removes the CEST, in col C the ,RIGHT formula picks out the right most charaters in the remaining data (col B) and in col D it is the combined formula, which seperates the whole lot from the original data in col A
regards
peter

[peterjuhnke]

hello again
please see if this is what you are after, I have copied the original sheet so you will now see "Arkusz1 (2)
Again this has been broken down in steps so you can see how the "Combined" formulas have been made up, so depending on what you actually want (see previous questions) we should be able to sort it out.
No doubt there is plenty of other ways to come up with the same answer, and some of the more experienced would know.
Regards
Peter

[afgi]

Dear Peter,

it looks fine, but does not work as I want. final goal is to sort it by date and time and make a graph.
so I need makro, which brakes it down into parts as you did, but also change the format of cells to date and time. at this stage, it is being seen as a text by excel. I need makro, because I have thousands of that kind of cells

[afgi]

hi,

i think this code is quite good path. it just need to be change to dynamic loop.

If you want to use VBA then one approach might be:

Please Login or Register  to view this content.

[afgi]

hi,
any new ideas?

[DonkeyOte]

The initial Evaluate route would adjust all applicable values in Col A simultaneously.

If that is not what you want please (in your next post) re-state all requirements in as much detail as you can.

I can't guarantee that I personally will be able to answer today but others will be able to do so if you provide sufficient information.

It is often a good idea to post a sample file with both "before" and "after" sheets so people can see exactly what it is you want.

[afgi]

Hi,

pls see the excel file attached. it contains 2 worksheets - before and after. so "before" is what I have in my table and "after" is what I want to have.
many thanks for your help.

[MarvinP]

Hi afgi,
See if the User Defined Functions work for you in this attached file. I've been reading up on the VBA Split Function: You may have to change languages to get the month numbers. I didn't know how to do that part.

Please Login or Register  to view this content.

Read about it at http://msdn.microsoft.com/en-us/libr...(v=VS.80).aspx
hope this helps.

[afgi]

Dear Marvin,

it works almost perfectly... it gets the data I want to have, however excel does not read it as the date or time, but as text. I need to make a graph that shows specific dates and times and to make this I need excel to have proper data in cells. pls see what I meen in attached file. many thanks

[afgi]

hi,

ok, I have managed to change data that I get from Function TimeExt (Column E) to the format that is readable for excel, pls see file attached.
I just need you to combin function from column E with function from Column J. I do not know if it is possible...

regarding the data that I get from Function DateExt, I do not know how to change name of the months (for example "jun"; "may"; "jun") to numbers. I need this to look this - 07/06/2010, not this 07/jun/2010. after change, we could apply similar functions, as in column J