asked on

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

Andrew Velasco

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

Andreamary

ASKER

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

Andrew Velasco

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

Andrew Velasco

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.

Andreamary

ASKER

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

Andrew Velasco

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

Andreamary

ASKER

Hi Andrew,

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

Andrea

Andrew Velasco

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?

Andreamary

ASKER

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

Andrew Velasco

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.

Andreamary

ASKER

Thanks for the fix on how to add a space. On # 2, I would like the period completely removed if possible.

Cheers,
Andrea

Andrew Velasco

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

Andreamary

ASKER

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

Andrew Velasco

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.

Andreamary

ASKER

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

ASKER CERTIFIED SOLUTION

Andrew Velasco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andreamary

ASKER

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

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

Cheers,
Andrea

Andrew Velasco

no problem Andrea - glad i could help! :)