Removing selected text from a cell

[excel_2009]

Hi excel gurus,

I was wondering if someone could help me

i have vast amounts of cells that contain information similar to the following (but the structure of the cells remains the same) e.g:

Books/Subjects/Children's Books/Harry Potter
Books/Subjects/Fiction Books/ Top Gear

All i want to do is simply delete anything after the 3rd "/" so that i end up with the following:

Books/Subjects/Children's Books/
Books/Subjects/Fiction Books/

(keeping in mind there are vast amounts of "subjects" e.g history/science etc i.e. it's not just for the above two cells)

Can someone please help me ??

Thank you

 

[jimrward]

assuming data starts in A1, try the following

=LEFT(A1,FIND("~",SUBSTITUTE(A1,"/","~",3)))

 

[excel_2009]

assuming data starts in A1, try the following

=LEFT(A1,FIND("~",SUBSTITUTE(A1,"/","~",3)))

Thank you, works perfectly

 

[excel_2009]

Try

=LEFT(A1,FIND("^",SUBSTITUTE(A1,"/","^",3)))

Where "^" is simply any string of your choice that is least likely to actually appear in the string.

Hope that helps.

Thank you! both methods are excellent

 

[hiker95]

excel_2009,


Sample data before the macro:

Excel Workbook
	A	B
1	Books/Subjects/Children's Books/Harry Potter
2	Books/Subjects/Fiction Books/ Top Gear
3
Sheet1

After the macro:

Excel Workbook
	A	B
1	Books/Subjects/Children's Books/Harry Potter	Books/Subjects/Children's Books/
2	Books/Subjects/Fiction Books/ Top Gear	Books/Subjects/Fiction Books/
3
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub DeleteAfter3()
' hiker95, 09/06/2011
' http://www.mrexcel.com/forum/showthread.php?t=576837
Dim A(), r As Long, Sp, s As Long, H As String
A = Range("A1").CurrentRegion.Resize(, 1)
For r = 1 To UBound(A)
  If InStr(A(r, 1), "/") > 2 Then
    Sp = Split(A(r, 1), "/")
    H = ""
    For s = LBound(Sp) To 2
      H = H & Sp(s) & "/"
    Next s
    A(r, 1) = H
  End If
Next r
Range("B1:B" & UBound(A)) = A
Columns(2).AutoFit
End Sub

Then run the DeleteAfter3 macro.

 

[hiker95]

excel_2009,


Slight update to the macro (same screenshots as above):


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub DeleteAfter3()
' hiker95, 09/06/2011
' http://www.mrexcel.com/forum/showthread.php?t=576837
Dim A(), r As Long, Sp, s As Long, H As String
A = Range("A1").CurrentRegion.Resize(, 1)
For r = 1 To UBound(A)
  If Len(A(r, 1)) - Len(Application.Substitute(A(r, 1), "/", "")) > 2 Then
    Sp = Split(A(r, 1), "/")
    H = ""
    For s = LBound(Sp) To 2
      H = H & Sp(s) & "/"
    Next s
    A(r, 1) = H
  End If
Next r
Range("B1:B" & UBound(A)) = A
Columns(2).AutoFit
End Sub

Then run the updated DeleteAfter3 macro.