Excel Help

[bluejays]

Okay, so I have a quick question. I have a file at work which basically has a column which logs some activity. Anyway, as an example, let's say the sentence says, "Contacted the client 02042012, client responded to e-mail at 22:14."

My question is, I want to find every time "client responded", and find the date that was in the string prior to the words "client responded", was pulled? It's slightly complicated I think, so help would be appreciated. Thanks.

[Ace]

Ctrl F , 'client responded' ?

[bluejays]

^ nope. It's not just one I have to look up. I need to pick it out for hundreds of columns, so I have to use the same formula and drag it.

[Ace]

You could try splitting the cells using space delimited. Before you do this do a replace of 'Client Responded' to 'ClientResponded' In which case each word should get it's own column, from there you should get ClientResponded in it's own column, and then you can sort from there and start reformatting.

It's not going to be perfect at first because some ClientResponded will be in column 'C'. And some in 'D', etc. but this shouldn't take long to sort and delete the unnecessary data to get it formatted correctly...

[Ace]

Originally Posted by fleury

^ nope. It's not just one I have to look up. I need to pick it out for hundreds of columns, so I have to use the same formula and drag it.

You originally said you had 'a' column that had this info in it, is that the case or is it all over the place?

[bluejays]

Sorry guys, maybe I wasn't clear. I have a column full of sentences (basically logs), not following the same standard conventions. However, anytime something like "client responded" is mentioned, a date will be listed before that. I want a formula to basically scan the cell, and identify if the cell contains "client responded", and return the date in there. I'll need to drag the formula downwards as there are many cells with this structure.

[Torture]

Something like this would do the trick:
=LEFT(A1,FIND("Client Responded",A1)-1)

Where A1 is the cell that has all your text. That will give you everything before "Client Responded".
IE If A1 was "Contacted Client on 020412 Client responded on insert date" it would return "Contacted Client on 020412" and then you could drag it down the sheet.

You'd have to make sure all the "Client Responded" are the same case but you could just do a find/replace to change all "client responded" to "Client Responded"

Ace's space deliminated way would probably do the trick as well.

[trew]

Originally Posted by fleury

Sorry guys, maybe I wasn't clear. I have a column full of sentences (basically logs), not following the same standard conventions. However, anytime something like "client responded" is mentioned, a date will be listed before that. I want a formula to basically scan the cell, and identify if the cell contains "client responded", and return the date in there. I'll need to drag the formula downwards as there are many cells with this structure.

Try something like this formula: =MID(A1,FIND("client responded",A1) -10,10)

(Where A1 is the cell reference for the item containing the text).

This will find the term "client responded" and will print the previous 10 characters.

[Shawnski]

I would do an if statement to find candidates:

Put this value into an open column to the right of your data (and propogate downwards accordingly)

=IF(ISERROR(SEARCH("client responded",X3)),0,1) where "X3" is the field you are searching.

This will return a "1" if the item is found, otherwise a "0" if it is not. Filter/sort accordingly.

You can use just the Search function, but if a value isn't found it returns an error.

[Shawnski]

Oh, and BTW if the "client responded" has been manually keyed in, you better hope there isn't a typo within it as those won't be picked up.

Excel is a calculator, Access is a database. You can only do so much with Excel before it becomes a data nightmare.

[Nage Waza]

Once you isolate the 'client responded' cells, you can then remove any letters and be left with the numbers. Besides the formulas, you can perform a go to search and copy all the cells at once with the value you are looking for. There are many ways to do this, but they are all hack jobs since the source of the data is basically organized very poorly.

As other people mentioned, free form text fields are bad. It is better to create three columns: Client Responded (Y/N), Contacted Client (ddmmyyyy), Client Responded (hh24:mm).

[bluejays]

Hey guys, thanks for your help. I'll test them all out and see what's best. I'm pretty good with excel, but for the most part never deal with strings of qualitative data, and so working with non-numerical is new to me. Fotze, it drives me nuts too, but in this case I'm helping out a co-worker where this stuff was another program generating a log in an Excel format. Thanks guys!

[nickerjones]

edit..