Converting Columns Cells To Certain Format

[privatemoon]

Hello,

I would like to convert Column A and Column R's cells using a few rules.

Conditions:

Column A:
Takes out the year (2017.2018,etc)
Takes out the parenthesis and it's contents (197236, etc, this number can be random)
Takes out the word 'Book'
Adds 'show' to the end (if it doesn't already exist)
Capitalizes everything in cell


Column R:
If it has only 'B®' (followed by something else), change entire cell to 'B®'
If it has 'B® Test' followed by something else, change it to 'TEST'


Before:


Column A:
Book Example Show (197236)
2017 Book Orange Tea (211953)
2017 Snack Valley (212169)
2017 Book Philadelphia (211958)


Column R:
B® Slick Example
B® Test Example
B® Classic Rocky Road
B® Test Rocky Road


After:


Column A:
EXAMPLE SHOW
ORANGE TEA SHOW
SNACK VALLEY SHOW
PHILADELPHIA SHOW


Column R:
B®
TEST
B®
TEST


Is this possible?

 

[Peter_SSs]

This overwrites the original Column A & R values so make sure you test on a copy of the data.

Sub Replace_Text()
  Dim a As Variant
  Dim i As Long
  
  'Column A
  With CreateObject("VBScript.RegExp")
    .Global = True
    .IgnoreCase = True
    .Pattern = "book ?|^\d{4} | ?\(\d*\)$"
    a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
    For i = 1 To UBound(a)
      If .Test(a(i, 1)) Then a(i, 1) = Replace(UCase(.Replace(a(i, 1), "") & " SHOW"), " SHOW SHOW", " SHOW")
    Next i
  End With
  Range("A1").Resize(UBound(a)).Value = a
  
  'Column R
  a = Range("R1", Range("R" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    If UCase(Left(a(i, 1), 3)) = "B®" Then
      If LCase(Mid(a(i, 1), 5, 4)) = "test" Then
        a(i, 1) = "TEST"
      Else
        a(i, 1) = "B®"
      End If
    End If
  Next i
  Range("R1").Resize(UBound(a)).Value = a
End Sub

 

[privatemoon]

This overwrites the original Column A & R values so make sure you test on a copy of the data.

Sub Replace_Text()
  Dim a As Variant
  Dim i As Long
  
  'Column A
  With CreateObject("VBScript.RegExp")
    .Global = True
    .IgnoreCase = True
    .Pattern = "book ?|^\d{4} | ?\(\d*\)$"
    a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
    For i = 1 To UBound(a)
      If .Test(a(i, 1)) Then a(i, 1) = Replace(UCase(.Replace(a(i, 1), "") & " SHOW"), " SHOW SHOW", " SHOW")
    Next i
  End With
  Range("A1").Resize(UBound(a)).Value = a
  
  'Column R
  a = Range("R1", Range("R" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    If UCase(Left(a(i, 1), 3)) = "B®" Then
      If LCase(Mid(a(i, 1), 5, 4)) = "test" Then
        a(i, 1) = "TEST"
      Else
        a(i, 1) = "B®"
      End If
    End If
  Next i
  Range("R1").Resize(UBound(a)).Value = a
End Sub

Hi,


The column A code works great. I'm having trouble with Column R's code when replacing "B®" and "test" with the actual words. Using this sample Column R data (has the same amount of letters and beginning letter of the actual words I plan on using):


Musicianshi® Physical Sample County
Musicianshi® Rocky Road
Figged Sample County
Mr.Biking Evolution
Modem Worldwide
Fitted Rocky Road


I've tried replacing "B®" with "Musicianshi®" and "test" with "physical" and then changing:

If UCase(Left(a(i, 1), 3)) = "B®" Then
If LCase(Mid(a(i, 1), 5, 4)) = "test" Then

To:

If UCase(Left(a(i, 1), 13)) = "Musicianshi®" Then
If LCase(Mid(a(i, 1), 15, 8)) = "physical" Then

That didn't work, but I'm not sure what's wrong. Even doing something as simple as replacing "B®" with "Try" and "test" with "bump" didn't work.


Also, how would I add a few more rules to the code? Like:
If it has 'Figged' (followed by something else), change entire cell to 'FIGGED'
If it has 'Mr.Biking' (followed by something else), change entire cell to 'MR.BIKING'
If it has 'Modem' (followed by something else), change entire cell to 'MODEM'
If it has 'Fitted' (followed by something else), change entire cell to 'FITTED'

 

[Peter_SSs]

Good news that column A is working. Seems like you gave a full and accurate description of the 'rules' for that column.

It is not surprising that column R is not working. The only 'rules' we have to go on are the ones you give us. The only sample data we have to go on is what you give us or describe to us.
In the case of both rules and samples for column R, the only thing mentioned as even a possibility in that column started with B®

Could you please start again with column R and gives us a full set of rules and a varied set of sample data and expected results. Both need to be enough for somebody who knows nothing about your data or requirements to understand both what you have and what you want.

 

[privatemoon]

Good news that column A is working. Seems like you gave a full and accurate description of the 'rules' for that column.

It is not surprising that column R is not working. The only 'rules' we have to go on are the ones you give us. The only sample data we have to go on is what you give us or describe to us.
In the case of both rules and samples for column R, the only thing mentioned as even a possibility in that column started with B®

Could you please start again with column R and gives us a full set of rules and a varied set of sample data and expected results. Both need to be enough for somebody who knows nothing about your data or requirements to understand both what you have and what you want.

Hi,


Sorry for the super late response! Here's a full set of rules/set of sample data with expected results.


Column R rules:
If it has only 'Musicianshi®' (followed by something else), change entire cell to 'Musicianships'
If it has 'Musicianshi® Physical' followed by something else, change it to 'Musicianships Physical'
If it has only 'Figged' (followed by something else), change entire cell to 'Figged'
If it has only 'Mr.Biking' (followed by something else), change entire cell to 'Mr.Biking'
If it has only 'Ms.Biking' (followed by something else), change entire cell to 'Ms.Biking'
If it has only 'Modem' (followed by something else), change entire cell to 'Modem'
If it has only 'Fitted' (followed by something else), change entire cell to 'Fitted'


Column R dataset example (has the same amount of letters and beginning letter of the actual words I plan on using):
Musicianshi® Physical Sample County
Musicianshi® Physical Sample Valley
Musicianshi® Rocky Road
Figged Sample County
Mr.Biking Evolution
Ms.Biking Oklahoma
Modem Worldwide
Fitted Rocky Road


Expected results for Column R:
Musicianships Physical
Musicianships Physical
Musicianships
Figged
Mr.Biking
Ms.Biking
Modem
Fitted


I hope this is enough, if it isn't please let me know. Thanks!

 

[privatemoon]

Bump, still looking for some help. Thanks.

 

[Peter_SSs]

With so many options (& unusual symbols) I'm not seeing any feasible solution.