-3

I exported a bunch of e-mail notifications into excel to do some analysis on them and need a bit of assistance pulling the values I need to separate cells.

Cell A2 Example Copy:

UserName submitted a chat request via the website. Their IP is 88.888.888.888 and they are using Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome or Safari to access the site. They entered the chat at Jul 20, 2018 2:43 AM.

From Cell A2, where the original body copy (Example is Block Quoted Above) is located in the sheet, I need to pull the IP Address into cell (B2), Date into cell (C2), Time into cell (D2) and Device Type into cell (E2) as Desktop or Mobile.

All the copy in A:A uses the same format/copy structure.

I have several hundred rows to go through with data like this. Thanks In Advance!!!

jameson_project
  • 27
  • 3
  • 1
    SO is not a "code for me" site. We do not generally provide code for the user, if you need this done you can hire a developer or freelancer. What we're here for is to help you with *existing* code or attempts. Please make some attempt at what you're trying to do, and let us know if it gives you any errors or problems. As your question stands, it's overly broad unfortunately. – BruceWayne Aug 21 '18 at 14:22
  • You can use a combination of `Search`, `Len`, `Left`, & `Right` excel equations to extract data. `Search` will return the character placing of a found string (and then you can extract using a combination of the other formulas). Although, you may need to use more. – urdearboy Aug 21 '18 at 14:25
  • Are all of the notifications in exactly the format you show? – Ron Rosenfeld Aug 21 '18 at 16:09
  • Sorry all, Yes the copy is in the same format on each entry. I was able to use a prior thread, linked in the answer below, to build out a RegEx for each of the needed variables. In the future, I will add more to the initial request to give a better foothold for assistance. – jameson_project Aug 21 '18 at 18:41

1 Answers1

0

Found a solution! Users Portland Runner & Wesanyer commented on an old thread how to setup RegEx and that actually turned out to be the best option. I expanded on that by using RegEx101.com to build out the full formulas I needed, and a bit of internal help from a co-worker. Below are the formulas I used to pull this info into separate cells in Excel. Thanks all!

  • IP Address: =regex($A2,"((\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}))", "$" & 1)
  • Date: =regex($A2,"([A-Za-z]{3} \d{1,2}, \d{4})", "$" & 1)
  • Time: =regex($A2,"(\d{1,2}:\d{2} (?:AM|PM))", "$" & 1)
  • Device: =IF(regex($A2,"(?:Windows NT|Macintosh|Linux)", "True"), "Desktop","Mobile")
jameson_project
  • 27
  • 3