Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Hello @Nilesandstuff,

Responding to the question asked in your comment to the answer provided by @Lupp, to automatically create a helper column with search and replace, you could use the methods CopyRange() and ReplacePattern() :

Sub CopyRange( strSourceRange As String, strTargetCell As String )
REM Copies the specified Source Range into a new Range that starts from the specified Target Cell.
REM <strSourceRange>    : specifies the Range to copy, e.g. "A1:B99".
REM <strTargetCell>     : specifies the starting Cell to put the result in, e.g. "D1". Must be a single cell.
    Dim oSheet As Object, oSourceRangeAddress As Object, oTargetCellAddress as Object
    oSheet = ThisComponent.CurrentController.ActiveSheet
    oSourceRangeAddress = oSheet.getCellRangebyName( strSourceRange ).getRangeAddress()
    oTargetCellAddress = oSheet.getCellRangebyName( strTargetCell ).CellAddress
    oSheet.copyRange( oTargetCellAddress, oSourceRangeAddress )
End Sub

Function ReplacePattern( strSourceRange As String, strPatternToFind As String, strPatternToReplace As String ) As Long
REM Replaces all occurrences of <strPatternToFind> with <strPatternToReplace>, within the specified CellRange.
REM <strSourceRange>:   e.g. "A1:A9999".
REM Returns the number of found/replaced rows.
    Dim oSheet As Object, oSource As Object, oReplace as Object
    oSheet = ThisComponent.CurrentController.ActiveSheet
    oSource = oSheet.getCellRangebyName( strSourceRange )
    oReplace = oSource.createReplaceDescriptor()
    oReplace.setSearchString( strPatternToFind )
    oReplace.setReplaceString( strPatternToReplace )
    oReplace.SearchType = 1     REM 0=search formulas; 1=search values; 2=search notes. [Calc only] 
    oReplace.SearchRegularExpression = True
    oReplace.SearchByRow = True
    ReplacePattern = oSource.replaceAll( oReplace )
End Function

For example suppose your columns are A and B, then you could automatically insert the helper columns in C and D respectively:

CopyRange( "A1:A999", "C1" )            REM  999 is the number of the last row to copy.
ReplacePattern( "C1:C999", ".*-", "" )

CopyRange( "B1:B999", "D1" )            REM  999 is the number of the last row to copy.
ReplacePattern( "D1:D999", "MG","" )
ReplacePattern( "D1:D999", "ML", "" )

Then you could Sort column C (Extended) and with "Options : Enable natural sort", then column D. The sorting could also be automated.