Function to strip unwanted text I don't think it is Trim

[Kurt]

Hello All,

What is the function to strip a line of text something like this:

file://Server/ServerHome01/UserID/Backup/Program Files/Microsoft Office/Office/Samples/Solutions.mdb

I want to delete all up to the UserID, cut that and paste it in a separate cell, I think want to take the info to the right and cut it and paste it in another adjacent cell on the same line.

As always thanks for any and all responses!

Kurt

 

[Kurt]

Boy I went the long way! I used the Convert Text to Columns in Excel 2007.

I would still like to learn the function equivalent if someone can show me how to combine the function and or use a vba macro that would be great!


Thanks,


Kurt

 

[Jonmo1]

Check out the Split Function.

Example in this UDF

Function getstring(c As Range, delim As String, nm As Long)
Dim mydata As Variant

mydata = Split(c, delim)
getstring = mydata(nm)
End Function

Now use a formula like

=GETSTRING(A1,"/",4)

That splits the string into parts seperated by /
Then returns the 4th item.

 

[Jonmo1]

To be more specific, it actually returns the 5th item (after the 4th /)
file: = 1st item (use 0 for the data before the first /)
nul value = 2nd item (use 1 for the data after the first /)
server = 3rd item (use 2 for the data after the 2nd /)
serverhome01 = 4th item
userid = 5th item