How to parse a string.....

Burrgogi

Burrgogi

Active Member
Joined
Nov 3, 2005
Messages
495
Office Version
  1. 2010
Platform
  1. Windows
I've been using Text to Columns to parse out a URL/filename string. It looks like this when I start out: (sample list)

In Col. A:
http://outboard.com/1234/198374_48579.jpg
http://outboard.com/12345/random file name_246679.jpg
http://outboard.com/1234/7744101_boats.jpg
http://www.kayak.com/12345/another random file name_48579.jpg
http://www.kayak.com/1234/2244109_paddles.jpg


Find what: http://outboard.com/
Replace with: ren "D:\Temp\

Find what: http://www.kayak.com/
Replace with: ren "D:\Temp\

Which almost gives me what I want. The problem is that there's various random numbers following the .com/ sometimes it's 4 digits long; but could be long as 6 digits long.

The result I'm trying to achieve is:

ren "D:\Temp\198374_48579.jpg"
ren "D:\Temp\random file name_246679.jpg"
ren "D:\Temp\7744101_boats.jpg"
ren "D:\Temp\another random file name_48579.jpg"
ren "D:\Temp\2244109_paddles.jpg"
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
How are you parsing it out?

Couldn't you use the Delimited option and specify / as the delimiter?

Then you could chose not to import (that's what it says) any fields you don't want.
 
Upvote 0
From what I can see of your samples, try changing to

Find what: http://outboard.com/*/
 
Upvote 0
It is a VBA downfall that it does not have a FindLast function.

You could try creating a function to do the work for you as in the following

HTML: 
Function LastOcurrance(strVal As String, strChar As String) As String
For I = Len(strVal) To 1 Step -1        'Start from the end of the string.
    If Mid(strVal, I, 1) = strChar Then
        LastOcurrance = Mid(strVal, I + 1, Len(strVal) - I)     'Return the file name
        Exit Function
    End If
Next I
End Function
To call the function :

HTML: 
Sub RetunFileName()
Dim FileName As String
         FileName = LastOcurrance("http://outboard.com/1234/198374_48579.jpg", "/")
End Sub
Hope its of some help.
 
Upvote 0
Peter_SSs said:
From what I can see of your samples, try changing to

Find what: http://outboard.com/*/
Click to expand...
In fact, if you are trying to replace all the URLs with "ren D:\Temp\" then you could try just changing the 'Find' part to
Find what: */
 
Upvote 0
Before Data>Text to Columns...

<TABLE style="WIDTH: 328pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=437><COLGROUP><COL style="WIDTH: 328pt; mso-width-source: userset; mso-width-alt: 15981" width=437><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 328pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 width=437>http://outboard.com/1234/198374_48579.jpg


</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>http://outboard.com/12345/random file name_246679.jpg</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>http://outboard.com/1234/7744101_boats.jpg</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>http://www.kayak.com/12345/another random file name_48579.jpg</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>http://www.kayak.com/1234/2244109_paddles.jpg


</TD></TR></TBODY></TABLE>

After Data>Text to Columns with / as the delimiter, skipping first 4 or 5 columns.

(4 if you select Treat consecutive delimiters as one.

<TABLE style="WIDTH: 328pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=437><COLGROUP><COL style="WIDTH: 328pt; mso-width-source: userset; mso-width-alt: 15981" width=437><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 328pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=437>198374_48579.jpg


</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>random file name_246679.jpg</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>7744101_boats.jpg</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>another random file name_48579.jpg</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>2244109_paddles.jpg


</TD></TR></TBODY></TABLE>

Or

Before:

<TABLE style="WIDTH: 376pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=501><COLGROUP><COL style="WIDTH: 328pt; mso-width-source: userset; mso-width-alt: 15981" width=437><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 328pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 width=437></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>Ren "D:\Temp\</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>http://outboard.com/1234/198374_48579.jpg


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Ren "D:\Temp\http://outboard.com/1234/198374_48579.jpg</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>http://outboard.com/12345/random file name_246679.jpg</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Ren "D:\Temp\http://outboard.com/12345/random file name_246679.jpg</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>http://outboard.com/1234/7744101_boats.jpg</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Ren "D:\Temp\http://outboard.com/1234/7744101_boats.jpg</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>http://www.kayak.com/12345/another random file name_48579.jpg</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Ren "D:\Temp\http://www.kayak.com/12345/another random file name_48579.jpg</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>http://www.kayak.com/1234/2244109_paddles.jpg


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2708436 class=xl67>Ren "D:\Temp\http://www.kayak.com/1234/2244109_paddles.jpg</TD></TR></TBODY></TABLE>

Formula in B2 - = $B$1 & A2 - copied down.

After:

<TABLE style="WIDTH: 228pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=304><COLGROUP><COL style="WIDTH: 180pt; mso-width-source: userset; mso-width-alt: 8777" width=240><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 180pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 width=240></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>Ren "D:\Temp</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>198374_48579.jpg


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Ren "D:\Temp\198374_48579.jpg</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>random file name_246679.jpg</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Ren "D:\Temp\random file name_246679.jpg</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>7744101_boats.jpg</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Ren "D:\Temp\7744101_boats.jpg</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>another random file name_48579.jpg</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Ren "D:\Temp\another random file name_48579.jpg</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>2244109_paddles.jpg


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2708436 class=xl67>Ren "D:\Temp\2244109_paddles.jpg</TD></TR></TBODY></TABLE>
 
Upvote 0
If using a formula then I wouldn't bother with Text to Columns as well - just get the formula to do the lot. B2 copied down.

Excel Workbook
AB
1Ren "D:\Temp\#"
2http://outboard.com/1234/198374_48579.jpgRen "D:\Temp\198374_48579.jpg"
3http://outboard.com/12345/random file name_246679.jpgRen "D:\Temp\random file name_246679.jpg"
4http://outboard.com/1234/7744101_boats.jpgRen "D:\Temp\7744101_boats.jpg"
5http://www.kayak.com/12345/another random file name_48579.jpgRen "D:\Temp\another random file name_48579.jpg"
6http://www.kayak.com/1234/2244109_paddles.jpgRen "D:\Temp\2244109_paddles.jpg"
Burrgogi
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
For Loop with If Statement Assistance
Replies
0
Views
335
smick
S
S
  • Question Question
Un-protecting Word Doc by Excel VBA for fillin and upload data to workbook automatically
Replies
0
Views
608
SqnLdrMona
S
C
  • Question Question
Conditional Formatting from several sheets using Ron de Bruin
Replies
1
Views
867
cactus_ritter
C
D
  • Question Question
Working with images in VBA - Image Properties
Replies
2
Views
8K
Jaafar Tribak
Jaafar Tribak
E
  • Solved
[VBA] File saving does not work/new version not uploaded
Replies
4
Views
747
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,224,508
Messages
6,179,189
Members
452,893
Latest member
denay

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top