PDA

View Full Version : Solved: deleting specific words from a cell



rafi_07max
12-07-2010, 08:38 AM
In column A of my workbook, the cells are filled with file destination

Take a look at the image below
http://www.iimmgg.com/image/05944595e06dc7fb8bf8cf02c11216d0

(http://www.iimmgg.com/image/05944595e06dc7fb8bf8cf02c11216d0)
So what I want to do is to keep only the filename and delete the rest of the details in the cell
For e.g. if the cell value is
C:\Documents and Settings\RMOHAMED\Desktop\board
I want to keep only the last word “board” and delete the rest of the words in the cell.
Take a look at the image below:
http://www.iimmgg.com/image/8385c1f588b1fdf8b9376223294e178e

I want to do this for the entire column A

I Have attached a sample workbook
5049

Simon Lloyd
12-07-2010, 08:51 AM
Assuming the words to find are in column A put this in B1 and copy down
=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

rafi_07max
12-07-2010, 06:37 PM
Thanks Simon for your help.
But it is not what I want. Sorry I didn’t describe in details.
What I want is, I have a button and I want to insert the codes in it. So once I click the button, it will automatically delete the unwanted words in column A
I want it to loop until the end of the column A that is filled with contents

Do you know how to do this?

shrivallabha
12-07-2010, 09:20 PM
Hello, use the code below in your button and change the sheet1 and the column to suit your needs.

Dim LastRow As Long
Dim MyData As Variant
Dim MyString As String
With Sheet1
LastRow = .Range("A1048576").End(xlUp).Row
For i = 1 To LastRow
MyData = Split(.Range("A" & i), "\")
MyString = MyData(UBound(MyData))
.Range("A" & i) = MyString
Next i
End With

Blade Hunter
12-07-2010, 09:58 PM
This what you are after?


Sub RemoveLastFolder()
'Set the types
Dim TempArray() As String
Dim X As Long
'Loop the cells in column A with data
For X = 1 To Range("A" & Rows.Count).End(xlUp).Row
'Put the contents in to an array of elements split with "\"
TempArray = Split(Range("A" & X).Text, "\")
'Redim the array to remove the last element
ReDim Preserve TempArray(UBound(TempArray) - 1)
'Join back to the cell
Range("A" & X).Formula = Join(TempArray, "\")
Next
End Sub


Cheers

Dan

Edit: Whoops, did it the wrong way round, threw away the last word, kept the rest ;)

rafi_07max
12-07-2010, 10:10 PM
Thanks Shrivallabha for your help but the codes have error

Thanks BladeHunter for your help,your codes did work but it did the opposite, It deleted the data which i wanted to keep and kept the data that i wanted to remove. Can you look at it again. Thank you.

Blade Hunter
12-07-2010, 10:11 PM
Sub RemoveLastFolder()
'Set the types
Dim TempArray() As String
Dim X As Long
'Loop the cells in column A with data
For X = 1 To Range("A" & Rows.Count).End(xlUp).Row
'Put the contents in to an array of elements split with "\"
TempArray = Split(Range("A" & X).Text, "\")
'Put the last element in the cell
Range("A" & X).Formula = TempArray(UBound(TempArray))
Next
End Sub


I have commented everything for you so you understand how it works.

Cheers

Dan

Blade Hunter
12-07-2010, 10:14 PM
Hello, use the code below in your button and change the sheet1 and the column to suit your needs.

Dim LastRow As Long
Dim MyData As Variant
Dim MyString As String
With Sheet1
LastRow = .Range("A1048576").End(xlUp).Row
For i = 1 To LastRow
MyData = Split(.Range("A" & i), "\")
MyString = MyData(UBound(MyData))
.Range("A" & i) = MyString
Next i
End With





I suspect the OP is on a version of excel prior to 2003 ie only has 65536 rows:

LastRow = .Range("A1048576").End(xlUp).Row
Instead of hard coding a row, use this
LastRow = .Range("A" & Rows.count).End(xlUp).Row

This will make it work on any excel.

shrivallabha
12-07-2010, 10:24 PM
Yes this should work on any workbook.
LastRow = .Range("A" & Rows.count).End(xlUp).Row


But what puzzles me more his attachment was an xlsx and NOT xls so I guessed the version was 2007 above. And the macro runs fine here on 2007.

Blade Hunter
12-07-2010, 10:26 PM
Yes this should work on any workbook.
LastRow = .Range("A" & Rows.count).End(xlUp).Row


But what puzzles me more his attachment was an xlsx and NOT xls so I guessed the version was 2007 above. And the macro runs fine here on 2007.

I didn't spot that but your right, it is extremely odd.

rafi_07max
12-07-2010, 10:34 PM
Thanks alot Shrivallabha and Blade Hunter for your time and help. It works :)