PDA

View Full Version : Solved: Add "<br />" before number.



hardcoremark
09-05-2012, 04:08 PM
Hello,

I run a website selling CDs, vinyl etc. and I upload a batch of products from a .csv file. One of the columns contains a product description or tracklisting or both. The website uses html so I want to add a line break <br /> to the tracklisting in each cell in this one column. So currently I have something like this;


1. Wake Up 2. The Fool I Am 3. Overrated 4. Let Go 5. Nicole 6. Talk Of The Town
7. Make This Count 8. This I Know 9. On The West Coast 10. Black And White

but I want it to look like this;


1. Wake Up <br />2. The Fool I Am <br />3. Overrated <br />4. Let Go <br />5. Nicole <br />6. Talk Of The Town <br />7. Make This Count <br />8. This I Know <br />9. On The West Coast <br />10. Black And White

Like I said in some cells there is a description before the tracklisting where I have something like this;


New Jersey's The Wait feature three members of the now defunct Endgame, pushing the sound of their former band to new heights. Compared to a modern day Texas Is The Reason or Farside, The Wait's melodic vocals are complemented by guitarist Scott Esbrandt's distinct style of playing that quickly made Endgame so popular. The album is reminiscent of the mid-'90s post-punk sound, but done perfectly and without imitation. 1. Passaic River High 2. Speaking Of Irish 3. Walk It Off 4. Route 7 5. Nothing Personal 6. Playing Dead 7. Ounces Away 8. Normal 9. My Last Employer

but I need it to be this;


New Jersey's The Wait feature three members of the now defunct Endgame, pushing the sound of their former band to new heights. Compared to a modern day Texas Is The Reason or Farside, The Wait's melodic vocals are complemented by guitarist Scott Esbrandt's distinct style of playing that quickly made Endgame so popular. The album is reminiscent of the mid-'90s post-punk sound, but done perfectly and without imitation. <br />1. Passaic River High <br />2. Speaking Of Irish <br />3. Walk It Off <br />4. Route 7 <br />5. Nothing Personal <br />6. Playing Dead <br />7. Ounces Away <br />8. Normal <br />9. My Last Employer

Any help to achieve this would be much appreciated.

Thanks,
Mark.

Teeroy
09-05-2012, 07:02 PM
Hi Mark,

The following should work for you.

Don't run the code more than once on a data set as you'll get repeated tags and, as in all cases with replacement, test on a COPY of the data.

Sub Add_Br_Tag()

Dim reg
Dim rng As Range
Dim m

Set reg = CreateObject("vbscript.regexp")
With reg
.Global = True
.IgnoreCase = True
.Pattern = "[^0-9][0-9]{1,2}\."
'change A to the column you want in the line below
For Each rng In Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
For Each m In .Execute(rng.Value)
sReplace = Left(m, 1) & "<br />" & Mid(m, 2, Len(m) - 1)
rng.Replace m, sReplace
Next m
Next
End With
End Sub

Bob Phillips
09-06-2012, 12:31 AM
What is your site?

snb
09-06-2012, 01:37 AM
or
if those data reside in colum E

sub snb()
for j=1 to 9
columns(5).replace " " & j, "<br />" & iif(j=1,"< br />","") & j
next
end sub

hardcoremark
09-06-2012, 03:23 AM
Hi Teeroy! That works perfectly!! Thankyou so much. Really appreciate your help. :bow:

Just one more thing. Is it possible to add an extra <br /> before the 1. when it appears after the description. So that I can have a blank line between the description and the tracklisting. Hope that makes sense!!

Also the find and replace function doesn't seem to work after I've run the VBA. Is that normal?

Hi xld. The site is punkrockcds.com

snb - thanks for the help and for pointing me in the direction of this forum. :beerchug:

Cheers,
Mark.

snb
09-06-2012, 03:41 AM
I amended my previous code.

Teeroy
09-06-2012, 05:09 AM
Hi Mark,

Simple change (not needing regular expression) since it affects one track number only.

Sub Add_Br_Tag()

Dim reg
Dim rng As Range
Dim m
Dim sReplace As String


Set reg = CreateObject("vbscript.regexp")
With reg
.Global = True
.IgnoreCase = True
.Pattern = "[^0-9][0-9]{1,2}\."
'change A to the column you want in the line below
For Each rng In Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
For Each m In .Execute(rng.Value)
sReplace = Left(m, 1) & "<br />" & Mid(m, 2, Len(m) - 1)
rng.Replace m, sReplace
Next m
rng.Replace "<br />1.", "<br /><br />1."
Next
End With
End Sub

hardcoremark
09-06-2012, 06:16 AM
Hi Teeroy. That is perfect!! :bow: :friends:

Thankyou very much!!
:beerchug: