CODEWITHSUNDEEP
excelvba
Gaby_ak
Gaby_ak

Reputation: 11

Applying a formula into an excel string

I want to add 50 to this the numeric portion in the middle of a string

I have 10000 strings to update. All of these starts like Smart/, than comes the number.

Smart/6420/CD-Cases

would become

Smart/6470/CD-Cases

Thanks and appreciate the help if it is doable

Upvotes: 0

Views: 88

Answers (3)

brettdj
brettdj

Reputation: 55672

Running a RegExp over a user selection with a variant array is a fast way to replace the results in-situ

Sub RegexReplace()
    Dim rng1 As Range
    Dim rngArea As Range
    Dim lngRow As Long
    Dim lngCol As Long
    Dim lngCalc As Long
    Dim objReg As Object
    Dim lngTemp As Long
    Dim X()


    On Error Resume Next
    Set rng1 = Application.InputBox("Select range for the replacement of leading zeros", "User select", Selection.Address, , , , , 8)
    If rng1 Is Nothing Then Exit Sub
    On Error GoTo 0

    'See Patrick Matthews excellent article on using Regular Expressions with VBA
    Set objReg = CreateObject("vbscript.regexp")
    With objReg
    .Pattern = "(Smart\/)(\d+)(.*)"
    .ignorecase = True
    .Global = False
    End With

   'Speed up the code by turning off screenupdating and setting calculation to manual
   'Disable any code events that may occur when writing to cells
    With Application
        lngCalc = .Calculation
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With

    'Test each area in the user selected range

    For Each rngArea In rng1.Areas
        'The most common outcome is used for the True outcome to optimise code speed
        If rngArea.Cells.Count > 1 Then
           'If there is more than once cell then set the variant array to the dimensions of the range area
           'Using Value2 provides a useful speed improvement over Value. On my testing it was 2% on blank cells, up to 10% on non-blanks
            X = rngArea.Value2
            For lngRow = 1 To rngArea.Rows.Count
                For lngCol = 1 To rngArea.Columns.Count
                    lngTemp = CLng(objReg.Replace(X(lngRow, lngCol), "$2")) + 50
                    X(lngRow, lngCol) = objReg.Replace(X(lngRow, lngCol), "$1" & lngTemp & "$3")
                Next lngCol
            Next lngRow
            'Dump the updated array back over the initial range
            rngArea.Value2 = X
        Else
            'caters for a single cell range area. No variant array required
            lngTemp = CLng(objReg.Replace(rngArea.Value, "$2")) + 50
            rngArea.Value = objReg.Replace(rngArea.Value, "$1" & lngTemp & "$3")
        End If
    Next rngArea

    'cleanup the Application settings
    With Application
        .ScreenUpdating = True
        .Calculation = lngCalc
        .EnableEvents = True
    End With

    Set objReg = Nothing
End Sub

Upvotes: 3

pnuts
pnuts

Reputation: 59442

Please try: 

=MID(A1,1,FIND("/",A1))&MID(A1,FIND("/",A1)+1,LEN(A1)-FIND("/",A1,FIND("/",A1)+1)-4)+50&MID(A1,FIND("/",A1,FIND("/",A1)+1),LEN(A1)-FIND("/",A1))

as tested only on the single example provided.

Another option would be to use Text to Columns with / as the delimiter to split the source into three pieces, before adding 50 to the middle piece and then stitching everything back together again with CONCATENATE.

Upvotes: 1

tmoore82
tmoore82

Reputation: 1875

The right solution depends on how much variation there is in your data. If they all start with "Smart/" and each number is 4 digits long, then the following would do what you want. I assume the data starts in A1. You could copy it down the 1000 rows you're dealing with.

=VALUE(MID(A1,7,4))+50

If there's variation, then you'll have to account for that by using FIND and LEN formulas to look for the / characters and trim the digit out. Here's an example: http://www.mrexcel.com/forum/excel-questions/444266-extract-string-between-two-characters.html.

If you have the option to use a macro, this would be much easier using VBA, where you could just use a Split function to split each value at the / character and grab the one in the middle. That would look something like this:

Public Sub AddFifty()
    Dim rng As Range
    Set rng = Range("A1:A1000")

    Dim tmp() As String

    For Each cell in rng.Cells
        tmp = Split(cell.Value2, "/")
        cell.Offset(0,1).Value2 = CLng(tmp(1)) + 50
    Next cell

End Sub

Upvotes: 2

Related Questions