Exract a Substring from a String within a Range

Question

I want to search a range for a string, then extract a substring from the string. The format of the string is xxx-xxx-xxx-xxx, where the x's may be letters or numbers. I am trying to identify the substring in reference to the "-". This code returns the "type mismatch" error on the line before End If.

For m = 7 To 16
    If InStr(EIRPBudget.Cells(m, 12), "-") Then
        FilterDescrip = EIRPBudget.Cells(m, 12)
        EIRPSummary.Range("V" & i + 5) = Split(FilterDescrip, "-")(0)
    End If
Next

Any assistance would be appreciated.

I've answered why the type mismatch occurs, but I don't know what the "i = 5" is supposed to be doing. Or is it a typo? — Mathieu Guindon
– Mathieu Guindon, Commented Aug 30, 2013 at 0:22

Mathieu Guindon · Accepted Answer · 2013-08-30 03:16:01Z

3

The Type Mismatch is caused by this part: EIRPSummary.Range("V" & i = 5) which is evaluated as "VFALSE" if i isn't 5 and "VTRUE" if it is...

UPDATE

I don't think you can get away with all-VBA without a loop, since you need to be iterating individual cells in a range. However you can modify your code to only evaluate the cell value once per iteration... but I don't think there's going to be a huge impact over the small number of iterations you're doing:

Dim cellValue As String
For m = 7 To 16
    cellValue = EIRPBudget.Cells(m, 12)
    If InStr(cellValue, "-") Then _
        EIRPSummary.Range("V" & i + 5) = Split(cellValue, "-")(0)
Next

Alternatively, if you can add another column in your worksheet, =LEFT([A1],FIND("-",[A1])-1) (where [A1] is the cell with "xxx-xxx-xxx-xxx") gives you just that: the n left-most characters up to the position of the first occurrence of "-", excluding that character.

edited Aug 30, 2013 at 3:16

answered Aug 30, 2013 at 0:20

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jmaz Over a year ago

I feel stupid. You caught a typo. The "=" should be a "+" and now it works fine. Thanks, pal. Maybe someone can explain a more efficient alternative that does not require a loop.

Mathieu Guindon Over a year ago

@Max I think I've found a loopless way.. which happens to be also codeless :)

jmaz Over a year ago

I like both implemenations. Thanks for taking the time.

Collectives™ on Stack Overflow

Exract a Substring from a String within a Range

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related