Solved

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

Posted on 2012-12-24
3
455 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 500 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

738 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