Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
WDP
 
Posts: n/a
Default Extract From Cell

Hi:

I have data in the following format:

\\192.100.1.94\documents and settings\user\my documents\file.pst

I want to extract the IP address from this line...ie....192.100.1.94

I guess I am looking for a function that will look for the only instance of
"\\" in the line....and then give me everything to the right (up to the 1st
instance of "\"

Can anyone help

Thanks
Warren Phillips
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gary''s Student
 
Posts: n/a
Default Extract From Cell

Select the cells and pull-down:

Data Text to Columns... and tell the wizard to use the backslash as the
separator.

This will split the text into parts.
--
Gary's Student


"WDP" wrote:

Hi:

I have data in the following format:

\\192.100.1.94\documents and settings\user\my documents\file.pst

I want to extract the IP address from this line...ie....192.100.1.94

I guess I am looking for a function that will look for the only instance of
"\\" in the line....and then give me everything to the right (up to the 1st
instance of "\"

Can anyone help

Thanks
Warren Phillips

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sloth
 
Posts: n/a
Default Extract From Cell

=MID(A1,3,FIND("\",A1,3)-3)

"WDP" wrote:

Hi:

I have data in the following format:

\\192.100.1.94\documents and settings\user\my documents\file.pst

I want to extract the IP address from this line...ie....192.100.1.94

I guess I am looking for a function that will look for the only instance of
"\\" in the line....and then give me everything to the right (up to the 1st
instance of "\"

Can anyone help

Thanks
Warren Phillips

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bpeltzer
 
Posts: n/a
Default Extract From Cell

If the format always begins with \\, then =MID(a1,3,FIND("\",a1,3)-3) give
you everything right of that up until the next \ (this will fail if there is
no subsequent \ character!).


"WDP" wrote:

Hi:

I have data in the following format:

\\192.100.1.94\documents and settings\user\my documents\file.pst

I want to extract the IP address from this line...ie....192.100.1.94

I guess I am looking for a function that will look for the only instance of
"\\" in the line....and then give me everything to the right (up to the 1st
instance of "\"

Can anyone help

Thanks
Warren Phillips

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
WDP
 
Posts: n/a
Default Extract From Cell

Everyone:

All these suggestions gave me the excact data I needed.

Thank you for your time in responding




"WDP" wrote:

Hi:

I have data in the following format:

\\192.100.1.94\documents and settings\user\my documents\file.pst

I want to extract the IP address from this line...ie....192.100.1.94

I guess I am looking for a function that will look for the only instance of
"\\" in the line....and then give me everything to the right (up to the 1st
instance of "\"

Can anyone help

Thanks
Warren Phillips

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula to extract a phone# from a cell of text (eg classified)? Reuel Excel Worksheet Functions 3 October 2nd 05 08:30 PM
Cell color based upon cell value My View Excel Discussion (Misc queries) 11 July 6th 05 03:59 AM
cell color index comparison MINAL ZUNKE New Users to Excel 1 June 30th 05 07:11 AM
Function required to extract cell info from an unopened file. westcountrytractorboy Excel Worksheet Functions 4 February 10th 05 08:57 AM
Extract date from cell Eric Excel Worksheet Functions 3 November 4th 04 07:37 PM


All times are GMT +1. The time now is 01:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"