Avatar of jkeegan123
jkeegan123
Flag for United States of America asked on

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

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!
Microsoft ExcelSQL

Avatar of undefined
Last Comment
jkeegan123

8/22/2022 - Mon
redmondb

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
ASKER CERTIFIED SOLUTION
Saqib Husain

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
jkeegan123

ASKER
@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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes