Formula for extracting data from a string

  • Thread starter Thread starter LFM
  • Start date Start date
L

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.
 
With your Server name in A1:

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

HTH,
Paul
 
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
 
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
 
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.
 
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.
 
Back
Top