| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extracting a string | Excel Discussion (Misc queries) | |||
| Extracting Numeric Data from a Delimited Text String | Excel Worksheet Functions | |||
| Extracting from a text string | Excel Worksheet Functions | |||
| extracting data from a text string of varying length | Excel Discussion (Misc queries) | |||
| extracting numbers from string | Excel Discussion (Misc queries) | |||
Hybrid Mode
