Remove Alpha & Multiply Macro

[Russ At Index]

Morning ,

I have the following data that i need to run a macro
based task with:

I extract a piece of data which has three alpha characters ,
28E15B1P ( E , B & P )

If it is possibe , i would like the operative to run a macro that
removes the E , B and the 1P , so we are left with 28 and 15 ,
then multiply the 28 by the 15.

So the numeric before the E is multilpied by the numeric after the
E but before the B

TIA

Russ

 

[JackDanIce]

Are the alphacharacters in question always going to be "E" and "B"?
Will the numbers you want to multiply be 2 digits long or of varying length?

 

[Russ At Index]

Hi JackDanIce,

The extract will always contain E , B & 1P

The numerics i would like to multiply in theory could
be varying length , from single digit up to three digits.

Thanks

Russ

 

[JackDanIce]

Hi Russ,

As formula, you can enter:

=(LEFT(A1,FIND("E",A1)-1)+0)*(MID(A1,FIND("E",A1)+1,FIND("B",A1)-FIND("E",A1)-1)+0)

And drag it down to the last row

As macro, try:

Sub ImNotNamingYou ()
 
Dim i As Long
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

Range("B1").Formula = "=(LEFT(A1,FIND(""E"",A1)-1)+0)*" _
    & "(MID(A1,FIND(""E"",A1)+1,FIND(""B"",A1)-FIND(""E"",A1)-1)+0)"
    
i = Range("A" & Rows.Count).End(xlUp).row
    
With Range("B1")
    .Formula = "=(LEFT(A1,FIND(""E"",A1)-1)+0)*" _
        & "(MID(A1,FIND(""E"",A1)+1,FIND(""B"",A1)-FIND(""E"",A1)-1)+0)"
    .AutoFill Range("B1:B" & i)
End With

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

 

[Russ At Index]

Hi JackDanIce,

Thanks Mate , runs like clock work , this will save
a heap of time !

Have a good day ,

Russ

 

[JackDanIce]

You're welcome, take care