Solved

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

Posted on 2012-12-24
3
439 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
3 Comments
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
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
Comment Utility
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
Comment Utility
@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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now