Formula for extracting data from a string
 

I have a spreadsheet of UNC drive mappings. I need to extract out
just the name of the server from the fields.

Example:
\\SERVER01\user
\\SRV02\user

I need to have a cell that only reads: SERVER01 and SRV02

The data entry is consistent with the backslashes "\\" then the server
name then "\" but the length of the server name can vary. I tried a
MID function, but since I do not have a consistent length, I need
something a bit better.

If anyone can assisst me in the proper formula, I would greatly
appreciate it.

Formula for extracting data from a string
 

=MID(A1,3,FIND("\",MID(A1,3,999))-1)
--
David Biddulph

"LFM" wrote in message
oups.com...
I have a spreadsheet of UNC drive mappings. I need to extract out
just the name of the server from the fields.

Example:
\\SERVER01\user
\\SRV02\user

I need to have a cell that only reads: SERVER01 and SRV02

The data entry is consistent with the backslashes "\\" then the server
name then "\" but the length of the server name can vary. I tried a
MID function, but since I do not have a consistent length, I need
something a bit better.

If anyone can assisst me in the proper formula, I would greatly
appreciate it.

Formula for extracting data from a string
 

With your Server name in A1:

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

HTH,
Paul

"LFM" wrote in message
oups.com...
I have a spreadsheet of UNC drive mappings. I need to extract out
just the name of the server from the fields.

Example:
\\SERVER01\user
\\SRV02\user

I need to have a cell that only reads: SERVER01 and SRV02

The data entry is consistent with the backslashes "\\" then the server
name then "\" but the length of the server name can vary. I tried a
MID function, but since I do not have a consistent length, I need
something a bit better.

If anyone can assisst me in the proper formula, I would greatly
appreciate it.

Formula for extracting data from a string
 

Hi try

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

where A1 is your drive descrip

--

_______________________
Naz,
London


"LFM" wrote:

I have a spreadsheet of UNC drive mappings. I need to extract out
just the name of the server from the fields.

Example:
\\SERVER01\user
\\SRV02\user

I need to have a cell that only reads: SERVER01 and SRV02

The data entry is consistent with the backslashes "\\" then the server
name then "\" but the length of the server name can vary. I tried a
MID function, but since I do not have a consistent length, I need
something a bit better.

If anyone can assisst me in the proper formula, I would greatly
appreciate it.

Formula for extracting data from a string
 

Try:

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

"LFM" wrote:

I have a spreadsheet of UNC drive mappings. I need to extract out
just the name of the server from the fields.

Example:
\\SERVER01\user
\\SRV02\user

I need to have a cell that only reads: SERVER01 and SRV02

The data entry is consistent with the backslashes "\\" then the server
name then "\" but the length of the server name can vary. I tried a
MID function, but since I do not have a consistent length, I need
something a bit better.

If anyone can assisst me in the proper formula, I would greatly
appreciate it.

Formula for extracting data from a string
 

How about EditReplace

What: \

With: nothing

Replace all.

If you want them both in same cell, enter =A1&" and " &A2 in an adjacent cell.



Gord Dibben MS Excel MVP


On 11 Apr 2007 14:12:13 -0700, "LFM" wrote:

I have a spreadsheet of UNC drive mappings. I need to extract out
just the name of the server from the fields.

Example:
\\SERVER01\user
\\SRV02\user

I need to have a cell that only reads: SERVER01 and SRV02

The data entry is consistent with the backslashes "\\" then the server
name then "\" but the length of the server name can vary. I tried a
MID function, but since I do not have a consistent length, I need
something a bit better.

If anyone can assisst me in the proper formula, I would greatly
appreciate it.

Formula for extracting data from a string
 

The OP probably didn't want the "user" part left in the string?
--
David Biddulph

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
How about EditReplace

What: \

With: nothing

Replace all.

If you want them both in same cell, enter =A1&" and " &A2 in an adjacent
cell.



Gord Dibben MS Excel MVP


On 11 Apr 2007 14:12:13 -0700, "LFM" wrote:

I have a spreadsheet of UNC drive mappings. I need to extract out
just the name of the server from the fields.

Example:
\\SERVER01\user
\\SRV02\user

I need to have a cell that only reads: SERVER01 and SRV02

The data entry is consistent with the backslashes "\\" then the server
name then "\" but the length of the server name can vary. I tried a
MID function, but since I do not have a consistent length, I need
something a bit better.

If anyone can assisst me in the proper formula, I would greatly
appreciate it.

Formula for extracting data from a string
 

Try this:

=SUBSTITUTE(SUBSTITUTE(A1,"\",""),"user","")


"LFM" wrote:

I have a spreadsheet of UNC drive mappings. I need to extract out
just the name of the server from the fields.

Example:
\\SERVER01\user
\\SRV02\user

I need to have a cell that only reads: SERVER01 and SRV02

The data entry is consistent with the backslashes "\\" then the server
name then "\" but the length of the server name can vary. I tried a
MID function, but since I do not have a consistent length, I need
something a bit better.

If anyone can assisst me in the proper formula, I would greatly
appreciate it.

Formula for extracting data from a string
 

If "user" can have more than one value, try this one-line UDF:

Function server(r As Range) As String
server = Split(r.Value, "\")(2)
End Function
--
Gary''s Student - gsnu200714


"LFM" wrote:

I have a spreadsheet of UNC drive mappings. I need to extract out
just the name of the server from the fields.

Example:
\\SERVER01\user
\\SRV02\user

I need to have a cell that only reads: SERVER01 and SRV02

The data entry is consistent with the backslashes "\\" then the server
name then "\" but the length of the server name can vary. I tried a
MID function, but since I do not have a consistent length, I need
something a bit better.

If anyone can assisst me in the proper formula, I would greatly
appreciate it.

Formula for extracting data from a string
 

Thank you to David, Toppers, Naz and Paul, all three varieties did the
trick. I've never played with the FIND function, so I'm going to go
play around with that one.

On Apr 11, 5:16 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
=MID(A1,3,FIND("\",MID(A1,3,999))-1)
--
David Biddulph

Formula for extracting data from a string
 

long story short - because the table that has the full information is
needed for its own functions, but in another area of the speadsheet I
need a table that only has the server name.

On Apr 11, 5:29 pm, Gord Dibben <gorddibbATshawDOTca wrote:
How about EditReplace

What: \

With: nothing

Replace all.

If you want them both in same cell, enter =A1&" and " &A2 in an adjacent cell.

Gord Dibben MS Excel MVP