[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 137
  • Last Modified:

Cleaning up variations on a specific string to make it consistent throughout a large worksheet

I need to clean up the placement of a specific 4-character acronym ("XXXX") in a large spreadsheet. Specifically, all instances of "XXXX" that occur at the beginning of a string only need to be moved to the last place in the string, preceded by a single space, and any extra spaces, dashes remaining at the beginning of the string after "XXXX" is moved is to be removed so that the row starts with the first complete word in that row. Any instances of "XXXX" that occur in any other position in the string except at the beginning are to be ignored.

Various examples of current format (does not include all possibilities):
1. XXXX - CITY TYPE
2. XXXX_CITY
3. XXXX-CITY-TYPE PROCEDURE HERE

Desired results:
1. CITY TYPE XXXX
2. CITY XXXX
3. CITY-TYPE PROCEDURE HERE XXXX

Thanks!
Andrea
0
Andreamary
Asked:
Andreamary
  • 10
  • 8
1 Solution
 
Andrew VelascoCommented:
Hi,

Does this work for you?

Sub CleanUpCity()
    Dim MySheet As Worksheet
    Dim iRow As Long, lastRow As Long, iCol As Long, lastCol As Long
    Dim strCity As String
    
    Set MySheet = Worksheets("YourWorksheetName")
    
    lastRow = MySheet.Range("A1").CurrentRegion.Rows.Count
    lastCol = MySheet.Range("A1").CurrentRegion.Columns.Count
    
    For iRow = 1 To lastRow
        For iCol = 1 To lastCol
            strCity = MySheet.Cells(iRow, iCol).Value
            If Left(strCity, 4) = "XXXX" Then
                strCity = Trim(Right(strCity, Len(strCity) - 4))
                If Left(strCity, 1) = "-" Or Left(strCity, 1) = "_" Then
                    strCity = Trim(Right(strCity, Len(strCity) - 1))
                End If
            End If
            MySheet.Cells(iRow, iCol).Value = strCity & " XXXX"
        Next iCol
    Next iRow
    
End Sub

Open in new window

0
 
AndreamaryAuthor Commented:
Hi Andrew,

I tried the code and it appeared to add "XXXX" at the end of every row in the spreadsheet, regardless of whether the row contained XXXX or not.

Andrea
0
 
Andrew VelascoCommented:
Sorry Andrea, I've moved the XXXX add to the If statement where it belongs.

Sub CleanUpCity()
    Dim MySheet As Worksheet
    Dim iRow As Long, lastRow As Long, iCol As Long, lastCol As Long
    Dim strCity As String
    
    Set MySheet = Worksheets("YourWorksheetName")
    
    lastRow = MySheet.Range("A1").CurrentRegion.Rows.Count
    lastCol = MySheet.Range("A1").CurrentRegion.Columns.Count
    
    For iRow = 1 To lastRow
        For iCol = 1 To lastCol
            strCity = MySheet.Cells(iRow, iCol).Value
            If Left(strCity, 4) = "XXXX" Then
                strCity = Trim(Right(strCity, Len(strCity) - 4))
                If Left(strCity, 1) = "-" Or Left(strCity, 1) = "_" Then
                    strCity = Trim(Right(strCity, Len(strCity) - 1)) & "XXXX"
                End If
            End If
            MySheet.Cells(iRow, iCol).Value = strCity
        Next iCol
    Next iRow
    
End Sub

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Andrew VelascoCommented:
Oh, you will need to add a space in front: " XXXX".  Sorry, I'm my phone and can't edit my comments.

Again, I apologize for the mix up on the original code.
0
 
AndreamaryAuthor Commented:
Thanks for the quick response, Andrew. I updated the code, including adding the space, and I am getting a "Type mismatch" error when I try and run the macro...

Andrea
0
 
Andrew VelascoCommented:
Sorry about that, my phone isn't very condusive to editing code:

Try this:
Sub CleanUpCity()
    Dim MySheet As Worksheet
    Dim iRow As Long, lastRow As Long, iCol As Long, lastCol As Long
    Dim strCity As String
    
    Set MySheet = Worksheets("YourWorksheetName")
    
    lastRow = MySheet.Range("A1").CurrentRegion.Rows.Count
    lastCol = MySheet.Range("A1").CurrentRegion.Columns.Count
    
    For iRow = 1 To lastRow
        For iCol = 1 To lastCol
            strCity = MySheet.Cells(iRow, iCol).Value
            If Left(strCity, 4) = "XXXX" Then
                strCity = Trim(Right(strCity, Len(strCity) - 4))
                If Left(strCity, 1) = "-" Or Left(strCity, 1) = "_" Then
                    strCity = Trim(Right(strCity, Len(strCity) - 1))
                End If
                 strCity=strCity & "XXXX"
            End If
            MySheet.Cells(iRow, iCol).Value = strCity
        Next iCol
    Next iRow
    
End Sub

Open in new window

0
 
AndreamaryAuthor Commented:
Hi Andrew,

I seem to still be getting "Type mismatch" with the latest code...

Andrea
0
 
Andrew VelascoCommented:
Hi Andrea,

I just tested on my machine using sample data and it ran through fine. Can you hit Debug and see if it is hitting the error on a specific cell value?
0
 
AndreamaryAuthor Commented:
Hi Andrew,

Thanks for this direction. I found 3 lines in my spreadsheet that has "#NAME?" so removed them and the macro ran. :-)

The macro is almost perfect...it just needs a couple of tweaks. I've noted the cross-section of results that have issues below:

Current macro's results:
1. CITY TYPEXXXX (needs a space before "XXXX")
2. .XXXX (there's a period before "XXXX"; should be no punctuation before "XXXX")
3. CITYXXXX (same issue as #1)
4. CITY-TYPE PROCEDURE HEREXXXX (same issue as #1)


Thanks!
Andrea
0
 
Andrew VelascoCommented:
Hi Andrea,

These should all be able to be fixed by adding a space to the "XXXX" string in line 19 of the sample code.

strCity = strCity & " XXXX"

Open in new window


Specifically on #2 - do you want the period to remain, just not directly in front of the acronym? (i.e. CITY. XXXX).  If so, then the above will fix that.

or would you rather have it completely removed? (i.e. CITY XXXX).  If not, let me know, and we should be able to make some adjustments.
0
 
AndreamaryAuthor Commented:
Thanks for the fix on how to add a space. On # 2, I would like the period completely removed if possible.

Cheers,
Andrea
0
 
Andrew VelascoCommented:
Hi Andrea,

I've added code to catch any NON-LETTER at the end of your string (prior to re-adding XXXX to the end).  This will take care of any punctuation, including periods.

I've also added comments to the code to help explain what each line is doing.

Sub CleanUpCity()
    Dim MySheet As Worksheet
    Dim iRow As Long, lastRow As Long, iCol As Long, lastCol As Long
    Dim blnStopLoop As Boolean
    Dim strCity As String
    
    Set MySheet = Worksheets("YourWorksheetName") 'Assign worksheet to the Worksheet Object MySheet
    
    lastRow = MySheet.Range("A1").CurrentRegion.Rows.Count 'Gets last row of data
    lastCol = MySheet.Range("A1").CurrentRegion.Columns.Count 'Gets last column of data
    
    For iRow = 1 To lastRow 'Starts a loop to cycle through every row of data
        For iCol = 1 To lastCol 'Starts a loop to cycle through every column of data (NOTE: since this is inside of the row loop, it will run for every row)
            strCity = MySheet.Cells(iRow, iCol).Value 'Makes strCity = the current cell value
            If Left(strCity, 4) = "XXXX" Then 'Checks for "XXXX" at the start of the value - if not then the it repastes the value and moves to the next column in the loop
                strCity = Trim(Right(strCity, Len(strCity) - 4)) 'Trim removes leading and following spaces - the right function gets everything to the right of the first 4 characters (XXXX)
                If Left(strCity, 1) = "-" Or Left(strCity, 1) = "_" Then 'Checks for hyphon and underscore at beginning of new string value (witout the XXXX)
                    strCity = Trim(Right(strCity, Len(strCity) - 1)) 'removes the hyphon or underscore if found
                End If
                
                blnStopLoop = False 'Boolean variable for ending the below Do Loop if entered
                
                If Not strCity = "" Then 'Checks if strCity is empty from previous Trims and removals
                    If Asc(Right(strCity, 1)) >= 65 And Asc(Right(strCity, 1)) <= 90 Then blnStopLoop = True 'If the last character of strCity is a lower case letter then loopStop is True
                    If Asc(Right(strCity, 1)) >= 97 And Asc(Right(strCity, 1)) <= 122 Then blnStopLoop = True 'If the last character of strCity is a upper case letter then loopStop is True
                Else
                    blnStopLoop = True 'If strCity is empty then loopStop is True
                End If
                
                Do Until blnStopLoop = True
                    strCity = Trim(Left(strCity, Len(strCity) - 1)) 'removes last character of strCity (previous checks for a letter as the last character failed
                    If Not strCity = "" Then
                        If Asc(Right(strCity, 1)) >= 65 And Asc(Right(strCity, 1)) <= 90 Then blnStopLoop = True 'If the last character of strCity is a lower case letter then loopStop is True
                        If Asc(Right(strCity, 1)) >= 97 And Asc(Right(strCity, 1)) <= 122 Then blnStopLoop = True 'If the last character of strCity is a upper case letter then loopStop is True
                    End If
                Loop
                
                 strCity = Trim(strCity & " XXXX") 'adds " XXXX" to the end of strCity
            End If
            MySheet.Cells(iRow, iCol).Value = strCity 'Makes the current cell = strCity
        Next iCol 'goes to next column in loop
    Next iRow 'goes to next row in loop
    
End Sub

Open in new window


Let me know if you have any more questions.

Thanks,

Andrew
0
 
AndreamaryAuthor Commented:
Hi Andrew,

Thanks for taking the time to add explanatory notes...much appreciated! I tried running the macro and unfortunately I am receiving the following error:

Run-time error '5':
Invalid procedure call or argument

Andrea
0
 
Andrew VelascoCommented:
Hi Andrea,

Can you hit debug and tell me what line is giving the error and what the cell value is?
It ran fine during testing yesterday with sample data.
0
 
AndreamaryAuthor Commented:
Hi Andrew,

I've created a sample spreadsheet and inserted the latest macro code, as I thought that might be easier for troubleshooting. With this sample spreadsheet when I click on the macro, it doesn't appear to run. I tried inserting the code in the worksheet, and as a module with the same outcome.

Thanks in advance for your assistance on this!

Cheers,
Andrea
EE-Sample-XXXXMacro.xlsm
0
 
Andrew VelascoCommented:
Hi Andrea,

Looks like we needed an empty string handler inside of the loop (Line 36 of the code).  Also, I did notice the sample sheet you provided me had empty rows (24-28).  This might be you removing data that was proprietary, but I did want to caution that the "CurrentRegion" properties used in Lines 9 & 10 to get last row and column rely on continuous set of data.  Similar to clicking in Cell A1 and doing a Select All.

Let me know if I can help any further.  :)

Andrew

Sub CleanUpCity()
    Dim MySheet As Worksheet
    Dim iRow As Long, lastRow As Long, iCol As Long, lastCol As Long
    Dim blnStopLoop As Boolean
    Dim strCity As String
    
    Set MySheet = Worksheets("CityData") 'Assign worksheet to the Worksheet Object MySheet
    
    lastRow = MySheet.Range("A1").CurrentRegion.Rows.Count 'Gets last row of data
    lastCol = MySheet.Range("A1").CurrentRegion.Columns.Count 'Gets last column of data
    
    For iRow = 1 To lastRow 'Starts a loop to cycle through every row of data
        For iCol = 1 To lastCol 'Starts a loop to cycle through every column of data (NOTE: since this is inside of the row loop, it will run for every row)
            strCity = MySheet.Cells(iRow, iCol).Value 'Makes strCity = the current cell value
            If Left(strCity, 4) = "XXXX" Then 'Checks for "XXXX" at the start of the value - if not then the it repastes the value and moves to the next column in the loop
                strCity = Trim(Right(strCity, Len(strCity) - 4)) 'Trim removes leading and following spaces - the right function gets everything to the right of the first 4 characters (XXXX)
                If Left(strCity, 1) = "-" Or Left(strCity, 1) = "_" Then 'Checks for hyphon and underscore at beginning of new string value (witout the XXXX)
                    strCity = Trim(Right(strCity, Len(strCity) - 1)) 'removes the hyphon or underscore if found
                End If
                
                blnStopLoop = False 'Boolean variable for ending the below Do Loop if entered
                
                If Not strCity = "" Then 'Checks if strCity is empty from previous Trims and removals
                    If Asc(Right(strCity, 1)) >= 65 And Asc(Right(strCity, 1)) <= 90 Then blnStopLoop = True 'If the last character of strCity is a lower case letter then loopStop is True
                    If Asc(Right(strCity, 1)) >= 97 And Asc(Right(strCity, 1)) <= 122 Then blnStopLoop = True 'If the last character of strCity is a upper case letter then loopStop is True
                Else
                    blnStopLoop = True 'If strCity is empty then loopStop is True
                End If
                
                Do Until blnStopLoop = True
                    strCity = Trim(Left(strCity, Len(strCity) - 1)) 'removes last character of strCity (previous checks for a letter as the last character failed
                    If Not strCity = "" Then
                        If Asc(Right(strCity, 1)) >= 65 And Asc(Right(strCity, 1)) <= 90 Then blnStopLoop = True 'If the last character of strCity is a lower case letter then loopStop is True
                        If Asc(Right(strCity, 1)) >= 97 And Asc(Right(strCity, 1)) <= 122 Then blnStopLoop = True 'If the last character of strCity is a upper case letter then loopStop is True
                    Else
                        blnStopLoop = True 'If strCity is empty (following start of loop), loopStop is True
                    End If
                Loop
                
                 strCity = Trim(strCity & " XXXX") 'adds " XXXX" to the end of strCity
            End If
            MySheet.Cells(iRow, iCol).Value = strCity 'Makes the current cell = strCity
        Next iCol 'goes to next column in loop
    Next iRow 'goes to next row in loop
    
End Sub

Open in new window

0
 
AndreamaryAuthor Commented:
Excellent! Works perfectly. And I'll make sure there are no blank rows.

Thanks very much for your patience and tenacity, Andrew...

Cheers,
Andrea
0
 
Andrew VelascoCommented:
no problem Andrea - glad i could help! :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now