Trim content of a column
 

I have a worksheet that contains multiple columns. The first Column has a
file path.
I would like to trim the path to only display the file name.


For example:
A
1 C:/test/folder/File1
2 C:/test/folder/File2
3 C:/test/folder/File3
4 C:/test/folder/File4
5 C:/test/folder/File5

Would like to display:

A
1 File1
2 File2
3 File3
4 File4
5 File5

Trim content of a column
 

Is the path always the same (wishful thinking, I suppose!)? If so, just use
search and replace: Find What is the path, including final backslash, leave
Replace With box empty.

On Wed, 31 Oct 2007 14:26:01 -0700, BZeyger
wrote:

1 C:/test/folder/File1
2 C:/test/folder/File2
3 C:/test/folder/File3
4 C:/test/folder/File4
5 C:/test/folder/File5

Trim content of a column
 

This little macro will do that. I assumed your data started in A1 and went
down. HTH Otto
Sub FindFileName()
Dim c As Long
Dim TheRng As Range
Dim i As Range
Set TheRng = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In TheRng
For c = 1 To 50
If Not InStr(Right(i.Value, c), "\") = 0 Then Exit For
Next c
i.Value = Right(i.Value, c - 1)
Next i
End Sub
"BZeyger" wrote in message
...
I have a worksheet that contains multiple columns. The first Column has a
file path.
I would like to trim the path to only display the file name.


For example:
A
1 C:/test/folder/File1
2 C:/test/folder/File2
3 C:/test/folder/File3
4 C:/test/folder/File4
5 C:/test/folder/File5

Would like to display:

A
1 File1
2 File2
3 File3
4 File4
5 File5

Trim content of a column
 

How about:

Select the range to fix
edit|replace
what: */
with: (leave blank)
replace all

Are you sure you have a slash and not a backslash?
Maybe you should replace *\ with (leave blank).



BZeyger wrote:

I have a worksheet that contains multiple columns. The first Column has a
file path.
I would like to trim the path to only display the file name.

For example:
A
1 C:/test/folder/File1
2 C:/test/folder/File2
3 C:/test/folder/File3
4 C:/test/folder/File4
5 C:/test/folder/File5

Would like to display:

A
1 File1
2 File2
3 File3
4 File4
5 File5

--

Dave Peterson

Trim content of a column
 

Hi, Otto:

I think the InstrRev function would eliminate the inner loop, i.e.

Sub FindFileName()
Dim c As Long
Dim TheRng As Range
Dim i As Range

Set TheRng = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In TheRng
c = InstrRev(i.Value, "\")
If c < 0 then i.value = Mid$(i.Value, c + 1)
Next i
End Sub

On Wed, 31 Oct 2007 18:37:51 -0400, "Otto Moehrbach"
wrote:

This little macro will do that. I assumed your data started in A1 and went
down. HTH Otto
Sub FindFileName()
Dim c As Long
Dim TheRng As Range
Dim i As Range
Set TheRng = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In TheRng
For c = 1 To 50
If Not InStr(Right(i.Value, c), "\") = 0 Then Exit For
Next c
i.Value = Right(i.Value, c - 1)
Next i
End Sub
"BZeyger" wrote in message
...
I have a worksheet that contains multiple columns. The first Column has a
file path.
I would like to trim the path to only display the file name.


For example:
A
1 C:/test/folder/File1
2 C:/test/folder/File2
3 C:/test/folder/File3
4 C:/test/folder/File4
5 C:/test/folder/File5

Would like to display:

A
1 File1
2 File2
3 File3
4 File4
5 File5

Trim content of a column
 

Myrna
Thank you for that. I have to show my ignorance and admit that I didn't
know about that function. Will this learning thing never end?? Otto
"Myrna Larson" wrote in message
...
Hi, Otto:

I think the InstrRev function would eliminate the inner loop, i.e.

Sub FindFileName()
Dim c As Long
Dim TheRng As Range
Dim i As Range

Set TheRng = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In TheRng
c = InstrRev(i.Value, "\")
If c < 0 then i.value = Mid$(i.Value, c + 1)
Next i
End Sub

On Wed, 31 Oct 2007 18:37:51 -0400, "Otto Moehrbach"

wrote:

This little macro will do that. I assumed your data started in A1 and
went
down. HTH Otto
Sub FindFileName()
Dim c As Long
Dim TheRng As Range
Dim i As Range
Set TheRng = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In TheRng
For c = 1 To 50
If Not InStr(Right(i.Value, c), "\") = 0 Then Exit For
Next c
i.Value = Right(i.Value, c - 1)
Next i
End Sub
"BZeyger" wrote in message
...
I have a worksheet that contains multiple columns. The first Column has a
file path.
I would like to trim the path to only display the file name.


For example:
A
1 C:/test/folder/File1
2 C:/test/folder/File2
3 C:/test/folder/File3
4 C:/test/folder/File4
5 C:/test/folder/File5

Would like to display:

A
1 File1
2 File2
3 File3
4 File4
5 File5

Trim content of a column
 

Myrna
If I may pick your brain for a minute, what is the function of the
dollar sign ($) after "Mid"? Thanks for your time. Otto
"Myrna Larson" wrote in message
...
Hi, Otto:

I think the InstrRev function would eliminate the inner loop, i.e.

Sub FindFileName()
Dim c As Long
Dim TheRng As Range
Dim i As Range

Set TheRng = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In TheRng
c = InstrRev(i.Value, "\")
If c < 0 then i.value = Mid$(i.Value, c + 1)
Next i
End Sub

On Wed, 31 Oct 2007 18:37:51 -0400, "Otto Moehrbach"

wrote:

This little macro will do that. I assumed your data started in A1 and
went
down. HTH Otto
Sub FindFileName()
Dim c As Long
Dim TheRng As Range
Dim i As Range
Set TheRng = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In TheRng
For c = 1 To 50
If Not InStr(Right(i.Value, c), "\") = 0 Then Exit For
Next c
i.Value = Right(i.Value, c - 1)
Next i
End Sub
"BZeyger" wrote in message
...
I have a worksheet that contains multiple columns. The first Column has a
file path.
I would like to trim the path to only display the file name.


For example:
A
1 C:/test/folder/File1
2 C:/test/folder/File2
3 C:/test/folder/File3
4 C:/test/folder/File4
5 C:/test/folder/File5

Would like to display:

A
1 File1
2 File2
3 File3
4 File4
5 File5

Trim content of a column
 

Dave
I just have to say, that is neat! Otto
"Dave Peterson" wrote in message
...
How about:

Select the range to fix
edit|replace
what: */
with: (leave blank)
replace all

Are you sure you have a slash and not a backslash?
Maybe you should replace *\ with (leave blank).



BZeyger wrote:

I have a worksheet that contains multiple columns. The first Column has a
file path.
I would like to trim the path to only display the file name.

For example:
A
1 C:/test/folder/File1
2 C:/test/folder/File2
3 C:/test/folder/File3
4 C:/test/folder/File4
5 C:/test/folder/File5

Would like to display:

A
1 File1
2 File2
3 File3
4 File4
5 File5

--

Dave Peterson

Trim content of a column
 

The $ character causes the function to return a String rather than a
Variant. It results in a (very slight) performance improvement.


"Otto Moehrbach" wrote in message
...
Myrna
If I may pick your brain for a minute, what is the function of the
dollar sign ($) after "Mid"? Thanks for your time. Otto
"Myrna Larson" wrote in message
...
Hi, Otto:

I think the InstrRev function would eliminate the inner loop, i.e.

Sub FindFileName()
Dim c As Long
Dim TheRng As Range
Dim i As Range

Set TheRng = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In TheRng
c = InstrRev(i.Value, "\")
If c < 0 then i.value = Mid$(i.Value, c + 1)
Next i
End Sub

On Wed, 31 Oct 2007 18:37:51 -0400, "Otto Moehrbach"

wrote:

This little macro will do that. I assumed your data started in A1 and
went
down. HTH Otto
Sub FindFileName()
Dim c As Long
Dim TheRng As Range
Dim i As Range
Set TheRng = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In TheRng
For c = 1 To 50
If Not InStr(Right(i.Value, c), "\") = 0 Then Exit For
Next c
i.Value = Right(i.Value, c - 1)
Next i
End Sub
"BZeyger" wrote in message
...
I have a worksheet that contains multiple columns. The first Column has
a
file path.
I would like to trim the path to only display the file name.


For example:
A
1 C:/test/folder/File1
2 C:/test/folder/File2
3 C:/test/folder/File3
4 C:/test/folder/File4
5 C:/test/folder/File5

Would like to display:

A
1 File1
2 File2
3 File3
4 File4
5 File5

Trim content of a column
 

Thanks Chip. Otto
"Chip Pearson" wrote in message
...
The $ character causes the function to return a String rather than a
Variant. It results in a (very slight) performance improvement.


"Otto Moehrbach" wrote in message
...
Myrna
If I may pick your brain for a minute, what is the function of the
dollar sign ($) after "Mid"? Thanks for your time. Otto
"Myrna Larson" wrote in message
...
Hi, Otto:

I think the InstrRev function would eliminate the inner loop, i.e.

Sub FindFileName()
Dim c As Long
Dim TheRng As Range
Dim i As Range

Set TheRng = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In TheRng
c = InstrRev(i.Value, "\")
If c < 0 then i.value = Mid$(i.Value, c + 1)
Next i
End Sub

On Wed, 31 Oct 2007 18:37:51 -0400, "Otto Moehrbach"

wrote:

This little macro will do that. I assumed your data started in A1 and
went
down. HTH Otto
Sub FindFileName()
Dim c As Long
Dim TheRng As Range
Dim i As Range
Set TheRng = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In TheRng
For c = 1 To 50
If Not InStr(Right(i.Value, c), "\") = 0 Then Exit For
Next c
i.Value = Right(i.Value, c - 1)
Next i
End Sub
"BZeyger" wrote in message
...
I have a worksheet that contains multiple columns. The first Column has
a
file path.
I would like to trim the path to only display the file name.


For example:
A
1 C:/test/folder/File1
2 C:/test/folder/File2
3 C:/test/folder/File3
4 C:/test/folder/File4
5 C:/test/folder/File5

Would like to display:

A
1 File1
2 File2
3 File3
4 File4
5 File5

Trim content of a column
 

I always do a save before I play with wildcards--just in case it doesn't do what
I want!

Otto Moehrbach wrote:

Dave
I just have to say, that is neat! Otto
"Dave Peterson" wrote in message
...
How about:

Select the range to fix
edit|replace
what: */
with: (leave blank)
replace all

Are you sure you have a slash and not a backslash?
Maybe you should replace *\ with (leave blank).



BZeyger wrote:

I have a worksheet that contains multiple columns. The first Column has a
file path.
I would like to trim the path to only display the file name.

For example:
A
1 C:/test/folder/File1
2 C:/test/folder/File2
3 C:/test/folder/File3
4 C:/test/folder/File4
5 C:/test/folder/File5

Would like to display:

A
1 File1
2 File2
3 File3
4 File4
5 File5

--

Dave Peterson

--

Dave Peterson

Trim content of a column
 

I agree. Always do a save first. Otto
"Dave Peterson" wrote in message
...
I always do a save before I play with wildcards--just in case it doesn't do
what
I want!

Otto Moehrbach wrote:

Dave
I just have to say, that is neat! Otto
"Dave Peterson" wrote in message
...
How about:

Select the range to fix
edit|replace
what: */
with: (leave blank)
replace all

Are you sure you have a slash and not a backslash?
Maybe you should replace *\ with (leave blank).



BZeyger wrote:

I have a worksheet that contains multiple columns. The first Column
has a
file path.
I would like to trim the path to only display the file name.

For example:
A
1 C:/test/folder/File1
2 C:/test/folder/File2
3 C:/test/folder/File3
4 C:/test/folder/File4
5 C:/test/folder/File5

Would like to display:

A
1 File1
2 File2
3 File3
4 File4
5 File5

--

Dave Peterson

--

Dave Peterson

Trim content of a column
 

Hi Otto and Myrna,

I must confess my ignorance because when I stepped through either of
these procedures, I could not see the file name--only the complete
path.

When I ran them, nothing changed in the spreadsheet.

I put
C:/test/folder/File1 in A1
C:/test/folder/File2 in A2
C:/test/folder/File3 in A3
C:/test/folder/File4 in A4
C:/test/folder/File5 in A5

Please show me what I'm missing.
Thanks,
Dan



Sub FindFileName()
Dim c As Long
Dim TheRng As Range
Dim i As Range
Set TheRng = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In TheRng
For c = 1 To 50
If Not InStr(Right(i.Value, c), "\") = 0 Then Exit For
Next c
i.Value = Right(i.Value, c - 1)
Next i
End Sub

Sub FindFileName()
Dim c As Long
Dim TheRng As Range
Dim i As Range

Set TheRng = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In TheRng
c = InstrRev(i.Value, "\")
If c < 0 then i.value = Mid$(i.Value, c + 1)
Next i
End Sub

Trim content of a column
 

Hi Dan

I think Otto used a back slash "\" in his code, which Myrna repeated.
You had a forward slash "/" in your example
Amend Myrna's code to use this and all will be well

Sub FindFileName()
Dim c As Long
Dim TheRng As Range
Dim i As Range

Set TheRng = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In TheRng
c = InstrRev(i.Value, "/")
If c < 0 then i.value = Mid$(i.Value, c + 1)
Next i
End Sub
--
Regards
Roger Govier



"dan dungan" wrote in message
oups.com...
Hi Otto and Myrna,

I must confess my ignorance because when I stepped through either of
these procedures, I could not see the file name--only the complete
path.

When I ran them, nothing changed in the spreadsheet.

I put
C:/test/folder/File1 in A1
C:/test/folder/File2 in A2
C:/test/folder/File3 in A3
C:/test/folder/File4 in A4
C:/test/folder/File5 in A5

Please show me what I'm missing.
Thanks,
Dan



Sub FindFileName()
Dim c As Long
Dim TheRng As Range
Dim i As Range
Set TheRng = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In TheRng
For c = 1 To 50
If Not InStr(Right(i.Value, c), "\") = 0 Then Exit
For
Next c
i.Value = Right(i.Value, c - 1)
Next i
End Sub

Sub FindFileName()
Dim c As Long
Dim TheRng As Range
Dim i As Range

Set TheRng = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In TheRng
c = InstrRev(i.Value, "\")
If c < 0 then i.value = Mid$(i.Value, c + 1)
Next i
End Sub

Trim content of a column
 

Thanks Roger!