Formula for extracting data from a string

[LFM]

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.

 

[David Biddulph]

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

 

[PCLIVE]

With your Server name in A1:

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

HTH,
Paul

 

[Guest]

Hi try

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

where A1 is your drive descrip

 

[Guest]

Try:

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

 

[Gord Dibben]

How about Edit>Replace

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

 

[David Biddulph]

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

 

[Guest]

Try this:

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

 

[Guest]

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

 

[LFM]

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.

 

[LFM]

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.