Pulling data out of a text field in excel...

[chanman525]

Hi everyone. I have lines of text that I'm trying to pull information out of. Here is what the lines look like...

ap1.0077.lowes.com [10.1.52.51]
ap10.0077.lowes.com [10.1.52.60]
ap11.0077.lowes.com [10.1.52.61]
ap12.0077.lowes.com [10.1.52.62]
ap13.0077.lowes.com [10.1.52.63]
ap15.0077.lowes.com [10.1.52.65]
ap16.0077.lowes.com [10.1.52.66]
ap17.0077.lowes.com [10.1.52.67]
ap18.0077.lowes.com [10.1.52.68]
ap19.0077.lowes.com [10.1.52.69]
ap2.0077.lowes.com [10.1.52.52]
ap20.0077.lowes.com [10.1.52.50]
ap21.0077.lowes.com [10.1.52.23]
ap22.0077.lowes.com [10.1.52.24]
ap23.0077.lowes.com [10.1.52.25]
ap24.0077.lowes.com [10.1.52.26]
ap25.0077.lowes.com [10.1.52.27]
ap3.0077.lowes.com [10.1.52.53]
ap4.0077.lowes.com [10.1.52.54]
ap5.0077.lowes.com [10.1.52.55]
ap6.0077.lowes.com [10.1.52.56]
ap7.0077.lowes.com [10.1.52.57]
ap8.0077.lowes.com [10.1.52.58]
asmi2.0077.lowes.com [10.1.52.224]
axon1.0077.lowes.com [10.1.52.211]
axon2.0077.lowes.com [10.1.52.212]
cabprt73.0077.lowes.com [10.1.52.183]
ctrprt77.0077.lowes.com [10.1.52.187]
ctrprt80.0077.lowes.com [10.1.52.190]
cusprt70.0077.lowes.com [10.1.52.180]
cusprt74.0077.lowes.com [10.1.52.184]
dcpdu1.0077.lowes.com [10.1.52.96]
dcpdu3.0077.lowes.com [10.1.52.98]
dsap1.0077.lowes.com [10.1.52.70]
dsap10.0077.lowes.com [10.1.52.79]

I'm trying to pull the device name (first set of characters until the first . ), the store number, which is 77, and the IP Address.

I can't figure out the best way to do this in Excel. Any help is appreciated.

Thanks.

 

[SkipVought]

Hi,

You can use the FIND() & LEFT() functions or, my favorite, use Data > Text to columns -- DELIMITED using the DECIMAL.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PeterMoran]

Hi,

Just in case you are still struggling with a solution, I had a go at your problem a few nights ago and came up with this result.

I was unable to get a reply on the forum when I had the result.

Please check out the attached file.

HTH

Peter Moran