Extracting values from a cell in MS Excel with mixed content
Extracting values from a cell in MS Excel with mixed content
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!!!
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 at 14:25
Search
Len
Left
Right
Search
Are all of the notifications in exactly the format you show?
– Ron Rosenfeld
Aug 21 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 at 18:41
1 Answer
1
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!
=regex($A2,"((d1,3.d1,3.d1,3.d1,3))", "$" & 1)
=regex($A2,"([A-Za-z]3 d1,2, d4)", "$" & 1)
=regex($A2,"(d1,2:d2 (?:AM|PM))", "$" & 1)
=IF(regex($A2,"(?:Windows NT|Macintosh|Linux)", "True"),
"Desktop","Mobile")
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
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 at 14:22