Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2012-12-24
3
Medium Priority
?
475 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
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

580 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