I have Data in workbook
1st Sheet= chat history which contains data Col “A” Sr. No. Col “B” is Body.
Task One
A. Create New Sheet “Break”
B. Copy all the data from Sheet “chat history” (Col “A” and “B”) to sheet “Break” in formula mode [ =SUBSTITUTE(CLEAN(TRIM('chat history'!A1)),CHAR(160),"") ]
C. In next column Col “C” copy and paste the data from formula mode to paste special values.
D. Start breaking the string In column C from D onwards.
E. Start breaking the string from the date and time starts (e.g. “08-03-2014 08:43:39 PM:” OR “14-09-2014 08:20:27 AM:” OR “24-11-2014 07:41:28 PM:” etc.) and end the break before the start of next date and time
(Data from the data and time to before the next data and time)
Task Two
A. Create New Sheet “Transpose”
B. Paste all the broken data (From Column D of Sheet Break to till data ends) in Sheet “Transpose” in Transpose mode
Task Three
A. Create New Sheet “Transpose All”
B. Arrange all the Transposed data in Sheet “Transpose ALL” in one column “B” with Heading Body and Col “A” with Sr. No. (Provide number too.)
C. Remove Duplicates in Column “B”
D. Name Col “C” as Sender
E. Extract the sent date in Col “A”(The starting point of String is date). If date is not there please ignore that particular row.
F. Extract the sender from Col “B” [ Sender is the string or the number immediately after the Date, time and : (E.g. (e.g. “08-03-2014 08:43:39 PM:” OR “14-09-2014 08:20:27 AM:” OR “24-11-2014 07:41:28 PM:” etc). Please refer example.
G. In Col “E” Extract all email id’s from Col ”B” and separate by “;”
H. In Col “F” Extract website address from Col ”B” and separate by “;”
I. In Col “G” Extract all cell numbers from Col ”B” and separate by “^” (Note Other than sender any number in the form of // starting with “+” and in between “?” other OR // any 10 digits number OR//Any 12 digit number starting with “+” (12 digit excluding “+”)OR//Any 11 digit number starting with “0” (11 digit excluding “0”)
Bookmarks