Formula for extracting data from a string

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LFM - 11 Apr 2007 22:12 GMT

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.

Reply to this Message

David Biddulph - 11 Apr 2007 22:16 GMT

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

Signature

David Biddulph

>I have a spreadsheet of UNC drive mappings. I need to extract out
> just the name of the server from the fields.
[quoted text clipped - 12 lines]
> If anyone can assisst me in the proper formula, I would greatly
> appreciate it.

Reply to this Message

LFM - 12 Apr 2007 03:51 GMT

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

Reply to this Message

PCLIVE - 11 Apr 2007 22:20 GMT

With your Server name in A1:

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

HTH,
Paul

Reply to this Message

Naz - 11 Apr 2007 22:24 GMT

Hi try

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

where A1 is your drive descrip

Signature

_______________________
Naz,
London

> I have a spreadsheet of UNC drive mappings. I need to extract out
> just the name of the server from the fields.
[quoted text clipped - 12 lines]
> If anyone can assisst me in the proper formula, I would greatly
> appreciate it.

Reply to this Message

Toppers - 11 Apr 2007 22:24 GMT

Try:

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

> I have a spreadsheet of UNC drive mappings. I need to extract out
> just the name of the server from the fields.
[quoted text clipped - 12 lines]
> If anyone can assisst me in the proper formula, I would greatly
> appreciate it.

Reply to this Message

Gord Dibben - 11 Apr 2007 22:29 GMT

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

>I have a spreadsheet of UNC drive mappings. I need to extract out
>just the name of the server from the fields.
[quoted text clipped - 12 lines]
>If anyone can assisst me in the proper formula, I would greatly
>appreciate it.

Reply to this Message

David Biddulph - 11 Apr 2007 22:37 GMT

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

Signature

David Biddulph

> How about Edit>Replace
>
[quoted text clipped - 25 lines]
>>If anyone can assisst me in the proper formula, I would greatly
>>appreciate it.

Reply to this Message

LFM - 12 Apr 2007 03:59 GMT

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.

> How about Edit>Replace
>
[quoted text clipped - 7 lines]
>
> Gord Dibben MS Excel MVP

Reply to this Message

Teethless mama - 11 Apr 2007 23:54 GMT

Try this:

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

> I have a spreadsheet of UNC drive mappings. I need to extract out
> just the name of the server from the fields.
[quoted text clipped - 12 lines]
> If anyone can assisst me in the proper formula, I would greatly
> appreciate it.

Reply to this Message

Gary''s Student - 12 Apr 2007 00:48 GMT

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

Signature

Gary''s Student - gsnu200714

> I have a spreadsheet of UNC drive mappings. I need to extract out
> just the name of the server from the fields.
[quoted text clipped - 12 lines]
> If anyone can assisst me in the proper formula, I would greatly
> appreciate it.

Reply to this Message