Display only certain text

[matthewdbeard]

I have file locations listed in many cells. Example:

folder1\folder2\folder3\folder4\folder5

I need a formula that will give me everything before the last "\"

So in this case I would only like to see

folder1\folder2\folder3\folder4

Any help is greatly appreciated

 

[AD_Taylor]

I can do this very easily with a UDF:

Function SplitString(text_value As String, delimiter As String, num_vals As Long) As String
'Splits a string (text_value) into a list of items using the delimiter to separate them
'A string is then returned containing the number of items specified (num_vals)
'
'SplitString takes 3 arguments:
'
'text_value = A string of items separated by a common delimiter e.g. cat,fish,bird,dog
'
'delimiter = The string separating the items, e.g. , [comma]
'
'num_vals = The number of items to return, e.g. 3 would return cat,fish,bird
'To refer to the number of items already in the text use a negative number
'E.g. -1 would return cat,fish,bird
'Since there were 4 items to start with, 4-1 = 3 items returned

    Dim strArr()                        As String
    Dim numRet                          As Long
    Dim i                               As Long
    
    strArr = Split(text_value, delimiter)
    i = LBound(strArr)
    
    If num_vals < 0 Then
        numRet = UBound(strArr) + 1 + num_vals
    Else
        numRet = num_vals
    End If
    
    Do Until i = numRet
        SplitString = SplitString & strArr(i) & delimiter
        i = i + 1
    Loop
    
    SplitString = Left(SplitString, Len(SplitString) - 1)
End Function

Then used in a cell like:

Excel 2010
	A
1	folder1\folder2\folder3\folder4\folder5
2	folder1\folder2\folder3\folder4
Sheet1

Cell Formulas
Range	Formula
A2	=SplitString(A1, "\", -1)

Personally though I'd like to still like to look for a formula solution rather than a VBA solution. It seems a bit much having to use code to solve this problem.

Hope this helps anyway!
Adam

 

[JoeMo]

Try this (A1 contains your folder text, B1 removes all beyond the last "\"):

Excel Workbook
	A	B
1	folder1\folder2\folder3\folder4\folder5	folder1\folder2\folder3\folder4
Sheet1

 

[matthewdbeard]

Is there a way to do the opposite....Here is an example

A1 Looks like this

<TABLE style="WIDTH: 119pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=159><COLGROUP><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5814" width=159><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 119pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=159>ALBMDSP303 fc1/11

I need fc1/11 in another cell..

But sometimes the fc looks like this fc10/13 so it's not always the same amount of letters
</TD></TR></TBODY></TABLE>

 

[matthewdbeard]

I found the solution

=RIGHT(A1,LEN(A1)-FIND(" ",A1))