VBA Question - Selecting a portion of a cell

[dhodges]

I have a large excel document with file paths for every file on our drive. Your typical cell looks like this S:\3x\ACCT\EXCEL\1RECEIPT.xls. I am trying to move the 1RECEIPT.xls to the column right beside it. I figured the easiest way would be to search for the last \ and copy everything after that but i am having some trouble figuring it out. Can anyone help?

 

[MrKowz]

Welcome to the forums!

Try:

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>S:\3x\ACCT\EXCEL\1RECEIPT.xls</TD><TD>1RECEIPT.xls</TD></TR></TBODY></TABLE>

Sheet1​

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B1</TH><TD style="TEXT-ALIGN: left">=REPLACE(A1,1,FIND("$$",SUBSTITUTE(A1,"\","$$",LEN(SUBSTITUTE(A1,"\","$$"))-LEN(A1))),"")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[dhodges]

Welcome to the forums!

Try:

Excel 2007<table style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" rules="all" cellpadding="2"><colgroup><col style="BACKGROUND-COLOR: #e0e0f0" width="25"><col><col></colgroup><thead><tr style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><th>
</th><th>A</th><th>B</th></tr></thead><tbody><tr><td style="COLOR: #161120; TEXT-ALIGN: center">1</td><td>S:\3x\ACCT\EXCEL\1RECEIPT.xls</td><td>1RECEIPT.xls</td></tr></tbody></table>

Sheet1​

<table style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" rules="all" cellpadding="2" width="85%"><tbody><tr><td style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<table style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" rules="all" cellpadding="2" width="100%"><thead><tr style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><th width="10">Cell</th><th style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</th></tr></thead><tbody><tr><th style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width="10">B1</th><td style="TEXT-ALIGN: left">=REPLACE(A1,1,FIND("$$",SUBSTITUTE(A1,"\","$$",LEN(SUBSTITUTE(A1,"\","$$"))-LEN(A1))),"")</td></tr></tbody></table></td></tr></tbody></table>

Works flawlessly Thank you!
my next question is, can i also automate the deleting of the file name after the \ ?

 

[MrKowz]

You mean delete the file from the drive, or just return the file path?

If you want to return the file path, then it is just a matter of reorganizing the formula:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">S:\3x\ACCT\EXCEL\1RECEIPT.xls</td><td style=";">1RECEIPT.xls</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">S:\3x\ACCT\EXCEL\1RECEIPT.xls</td><td style=";">S:\3x\ACCT\EXCEL\</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=REPLACE(<font color="Blue">A1,1,FIND(<font color="Red">"$$",SUBSTITUTE(<font color="Green">A1,"\","$$",LEN(<font color="Purple">SUBSTITUTE(<font color="Teal">A1,"\","$$"</font>)</font>)-LEN(<font color="Purple">A1</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
If you are actually wanting to delete the file from the hard drive, that is quite a bit more in depth.

 

[dhodges]

I am sorry, i should have been more clear. The first formula copys the file name perfectly, but i am looking to copy the file name from A1 and paste it in B1. Then delete the Filename from A1. So

A1 ____________________________ B1
S:\3x\ACCT\EXCEL\1RECEIPT.xls ___ 1RECEIPT.xls

Changes to

A1 _____________________________B1
S:\3x\ACCT\EXCEL\ _______________1RECEIPT.xls

Sorry the _______ represents spacing, not a underscore.

Thanks an advance!

 

[MrKowz]

Try running this macro:

Public Sub StripFilename()
Dim i       As Long, _
    j       As Long, _
    LR      As Long
    
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    Range("B" & i).Value = Application.Replace(Range("A" & i).Value, 1, InStr(Application.Substitute(Range("A" & i).Value, "\", "$$", _
                                                Len(Application.Substitute(Range("A" & i).Value, "\", "$$")) - Len(Range("A" & i).Value)), "$$"), "")
    Range("A" & i).Value = Left$(Range("A" & i).Value, InStr(Application.Substitute(Range("A" & i).Value, "\", "$$", Len(Application.Substitute(Range("A" & i).Value, "\", "$$")) - Len(Range("A" & i).Value)), "$$"))
Next i
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationManual
End With
End Sub

 

[dhodges]

Wow....Thank you so much. Once again it worked Flawlessly!

 

[MrKowz]

Edit - Adjust your code to the following:

Public Sub StripFilename()
Dim i       As Long, _
    j       As Long, _
    LR      As Long
    
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    Range("B" & i).Value = Application.Replace(Range("A" & i).Value, 1, InStr(Application.Substitute(Range("A" & i).Value, "\", "$$", _
                                                Len(Application.Substitute(Range("A" & i).Value, "\", "$$")) - Len(Range("A" & i).Value)), "$$"), "")
    Range("A" & i).Value = Left$(Range("A" & i).Value, InStr(Application.Substitute(Range("A" & i).Value, "\", "$$", Len(Application.Substitute(Range("A" & i).Value, "\", "$$")) - Len(Range("A" & i).Value)), "$$"))
Next i
With Application
    .ScreenUpdating = True
    [COLOR=red][B].Calculation = xlCalculationAutomatic[/B][/COLOR]
End With
End Sub

 

[dhodges]

What does that change? Because i ran the first macro and the results came out as i wanted. Just curious in an attempt to learn as much about VBA as possible.

 

[MrKowz]

What does that change? Because i ran the first macro and the results came out as i wanted. Just curious in an attempt to learn as much about VBA as possible.

When running the macro, it turns off automatic worksheet calculations (Application.Calculation = xlCalculationManual). However, at the end of the macro, we need to tell it to turn the automatic worksheet calculations back on (Application.Calculation = xlCalculationAutomatic). Otherwise, you will have to press F9 to calculate your worksheet (or go to Tools>Options>Calculation and turn the automatic calculations back on).