Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel DATA PARSING issue - using LEFT, MID, RIGHT, and FIND operators

Posted on 2012-12-24
3
Medium Priority
?
465 Views
Last Modified: 2013-01-02
I am trying to get an excel data parsing formula correct and I am having some difficulty.  I'm not sure if the difficulty is in my understanding the syntax of the command/operator, or if the documentation of the operator is incorrect.

I have a batch file that is gathering information from PC's and servers as users log into them....the information being gathered is:  Date of logon, Time of logon, Username logging on, PC/Server being logged on to, IP Address of system, MAC of system.

Unfortunately, this is being logged WITHOUT delimiters between the entries...and while this is not usually a problem, the SPACES in the NAME of the NIC giving the IP address is causing issues when I use SPACE as the delimiting character.

The data all exists in lines like the following:

Fri 12/21/2012  6:23:43.07 jkeegan CTWS07.ctny.local Intel(R) 82566DM-2 Gigabit Network Connection {192.168.0.225} 00:1E:4F:D2:1A:34

Note that the IP address always appears in curly braces...I use this character as part of the formula.  All of the data imports into EXCEL in column 1.  If I use a formula on column 2 to parse data, I don't get exactly what I want....I'm trying to get:  

DATE - TIME - USER - PC - IP - MAC-address

In independent columns.  

I'm trying to use LEFT and FIND in a nested command and it's reporting that I am using the command improperly.  Here's what I tried:

=LEFT(A1,FIND("}",A1,1),FIND("{",A1,1))

and I get:  [b]You've entered too many arguments for this function.[/b]  I thought that the 3rd argument of the LEFT function was where to START, and that if the 3rd argument was left out, that it was assumed that START was at position (1) of the search cell.  

This is where I am confused...Excel acts as if there is NOT a 3rd argument to the LEFT function.  If I use the above mentioned excel formula and take out the 3rd argument, leaving only 2, I get correct results, however the results are EVERYTHING up to the end of the closing curly braces, rather than JUST the ip address inside the curly braces.

What I WANT excel to do is the following:

Pull out the USERNAME into a cell / column
Pull out the COMPUTERNAME into a cell / column
Pull out the IP address (without the curly braces) into a cell / column

Maybe I need to use the MID or RIGHT operator instead of LEFT and FIND?

Help is appreciated, thanks!
0
Comment
Question by:jkeegan123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 38719473
Hi, jkeegan123.

Please see attached. A few points...
(1) I assume that three letters are used for days and that curly braces only occur around the IP address..
(2) The formulas aren't very pretty and are vulnerable to small changes in the string.
(3) A macro would be a much safer option. Are you interested?

Edit: Oh, and the date may be a problem unless you're using mm/dd/yyyy. (If so, and the cell shows an error, just type a space at the end of the formula and hit Enter.)

Regards,
Brian.Split-Fields.xls
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 total points
ID: 38719623
The LEFT() and RIGHT() functions take two arguments each. The first argument is the string or a reference to it; and the second is the number of characters from the left and right respectively.

The mid function takes three; the string, the starting character and the number of characters.
0
 
LVL 5

Author Comment

by:jkeegan123
ID: 38722818
@Brian:  Curly braces for IP addresses only, correct.  Date is DEFINITELY always MM/DD/YYYY.

@SSAQIBH:  I'll try my first attempt using teh MID function...I was basing my try off of a well written tutorial (I thought!) that explained LEFT as having (3) arguments.

I ended up pulling just the IP from the line with this:

=MID(A1,FIND("{",A1,1),(FIND("}",A1,1))-(FIND("{",A1,1))+1)

I'll see if I can get the rest using similar operators, now that I know MID pulls what I need providing you can identify it...pretty slick.
0

Featured Post

Major Serverless Shift

Comparison of major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question