0

I have alpha numeric text in each cell in a column and want to get the product of the numbers of each cell ignoring the alphabet characters with help of formula. No of characters in each cell is different example:

24R2 should return product of 48
7S11 should return product of 77
12R4 should return product of 48
2P6 should return product of 12

and so on. Is there a simple function to do this?

2
  • 1
    "Is there a simple function to do this?" no
    – Alex M
    Jan 17, 2020 at 17:05
  • are you okay with more complex answers assuming a "simple function" doesn't exist?
    – Doktor J
    Jan 17, 2020 at 19:41

5 Answers 5

0

Well I certainly wouldn't call it simple but this is what I came up with:

=LEFT(A1,IF(LEN(A1)=0,0,MIN(IF(1*ISNUMBER(1*MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1))=0,ROW(INDIRECT("A1:A"&LEN(A1))),LEN(A1)+1)))*(ISNUMBER(A1)=FALSE)-1)*RIGHT(A1,LEN(A1)-IF(LEN(A1)=0,0,MIN(IF(1*ISNUMBER(1*MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1))=0,ROW(INDIRECT("A1:A"&LEN(A1))),LEN(A1)+1)))*(ISNUMBER(A1)=FALSE))

Where A1 contains the value.

How it works:

=IF(LEN(A1)=0,0,MIN(IF(1*ISNUMBER(1*MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1))=0,ROW(INDIRECT("A1:A"&LEN(A1))),LEN(A1)+1)))*(ISNUMBER(A1)=FALSE)

Will get you the first alpha character index of a string.

And then its just a matter of plugging that index into:

=LEFT(A1,(alpha index) -1)*RIGHT(A1,LEN(A1)-(alpha index))

to parse out the numbers on the left and right side and then multiply them together.

0

It's not pretty but a combination of formulas can do it. You could also spend time merging them into one, if you needed it that way. These formulas are done assuming the first number are either 1 or two digits, there is only one letter followed by another 1 or 2 digits.

enter image description here

Hopefully this helps,

Brad

0

If the dividing letter might have an E, it is no need to replace it with another letter,

Just add "E0" to the end of the formula, something like this :

In B1, formula copied down :

=LOOKUP(9^9,0+(LEFT(A1,ROW($1:$99))&"E0"))*LOOKUP(9^9,0+(RIGHT(A1,ROW($1:$99))&"E0"))

enter image description here

0

Not simple, but maybe a bit simpler, so long as the dividing letter is not an E

=AGGREGATE(14,6,--LEFT(A1,ROW(INDIRECT("1:"& LEN(A1)))),1)*
AGGREGATE(14,6,--RIGHT(A1,ROW(INDIRECT("1:"& LEN(A1)))),1)

If the dividing letter might be an E, then we need to replace it if present:

=AGGREGATE(14,6,--LEFT(SUBSTITUTE(A1,"E","X"),ROW(INDIRECT("1:"& LEN(A1)))),1)*
AGGREGATE(14,6,--RIGHT(SUBSTITUTE(A1,"E","X"),ROW(INDIRECT("1:"& LEN(A1)))),1)

The SUBSTITUTE function will only have an effect if an E is present. If there is no E, then the formula works as does the first one.

enter image description here

0

VBA has at least two advantages over a worksheet formula that uses only native worksheet functions.

  • The Val function will read a prefix numerical value up to an alphabetic character without being supplied the position of the alphabetic character. e.g. Val("24R2") returns 24.
  • VBA has a native Split function that quickly divides your prefix and suffix numerical values into a variant array using the alphabetic character as a delimiter.

VBA User Defined Function to be placed in a standard module code sheet (e.g. Module1):

Option Explicit

Function sumBits(ByVal str As String) As Long

    Dim ems As Variant

    ems = Split(str, Mid(str, Len(CStr(Val(str))) + 1, 1))

    sumBits = ems(0) * ems(1)

End Function

enter image description here
  Click for larger image

Caveat: This presumes no leading zeroes in the prefix numerical value.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .