| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trim content of a column
Thanks Roger!
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Trim varying decimals from column of numbers | Excel Worksheet Functions | |||
| How to transpose formulas from column content to row content. | Excel Worksheet Functions | |||
| How to apply the trim function to a whole column. | Excel Programming | |||
| VBA Trim and Application.worksheetfunction.Trim | Excel Programming | |||
| AUTOMATICALLY CHOOSE COLUMN H OR G DEPENDANT ON CONTENT IN COLUMN. | Excel Programming | |||
Linear Mode
