1

I have a table that stores both a number and some text in a cell, and I want to do an arithmetic operation based on the numbers. So my table looks like this:

Number1       | Number2       | Product
2             | 3             | 6
3 (some text) | 40            | 120
4             | 5 (here too)  | 20

So in the presence of those ugly texts, I still want to be able to produce column 3 automatically. I guess I need a function that parses the substring upto the first non-numeric character into a number. Can I do this with Excel?

P.S.: The numbers can have more than 1 digits. Safe to assume they are integers, but it would be nice to see a solution that applies to floating points too :)

Thanks,

  • The ideal solution, of course, is to not mix numbers and text in a cell that you want to use for arithmetic. Is splitting the first two columns out of the question? – fixer1234 Jun 18 '16 at 19:15
  • By splitting, you mean dividing the numbers and text? Well it is certainly an option (maybe the elegant one :)) another one could be using comments to store text info, but I still wonder if I could handle this programmatically. – jeff Jun 18 '16 at 19:55
3

To get the numbers if they are in the beginning of the string we can use this:

=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))),1))

as our base formula, This will find the end of the number and return that as the end of the MID() Function.

There is a lot going on here:

SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},""))) As this part iterates through the numbers it is replacing the last instance of each number with }}}.

The third criterion of SUBSTITUTE is the instance. We find the number of instances with the LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")). It iterates through the numbers and replaces each one at a time with nothing. It then finds the difference in length of the original string and the new one.

So in the case of A2 when it iterates to the 2 it finds 2 and the outer Substitute replaces the last one with }}}. This is just a temprorary place holder.

The Aggregate function is a multi function function. The 14 tells the funtions we are using the Large() function. The 6 tells the function to ignore errors. This is important in that many of the iteration will not find anything and return an error.

With the 1 at the end it tells the function we want the highest return from the Search function which searches for those temporary }}} that are placed through iteration on the last instance of each number.

So the Aggregate returns the max number found. Which we pass to the length criterion in the Mid function.

So we now have found the number at the front of the string.

So we can multiply two of these together to get the desired output(Any math function will turn the returned string into a number):

=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))),1))*MID(B2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"")))),1))

enter image description here

One Caveat The Aggregate function was introduced in Excel 2010. It may not work with older versions.

If you have an older version you will need to use this longer formula:

=MID(A2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},""))))),SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))))))*MID(B2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},""))))),SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"")))))))

It does roughly the same as the one above accept it must test for the errors first before finding the max.

  • Can you add a short explanation of how this works? – fixer1234 Jun 19 '16 at 1:14
  • @fixer1234 fixed it. See edit. – Scott Craner Jun 19 '16 at 2:19
  • Sorry I asked for an explanation. This made my brain explode. :-) But it looks like it works, so +1. – fixer1234 Jun 19 '16 at 2:36
  • @fixer1234 glad you did, it made me realize my first attempt was flawed and needed a rework. – Scott Craner Jun 19 '16 at 2:37
  • @fixer1234 if you think that one was complicated see here: stackoverflow.com/questions/37870970/… I am not about to dissect that one it would take a book. – Scott Craner Jun 19 '16 at 2:41
1

Assuming the number is always separated from any text by a space, you could use something like this. Say your example has data starting in A2, so your first result in C2 would ordinarily look like:

=A2*B2

We need to replace the simple cell references with a formula that treats the source cell as a number if it's only a number, but extracts the number if there's also text. So C2 would become:

=IF(ISNUMBER(A2),A2,LEFT(A2,FIND(" ",A2)-1)) * IF(ISNUMBER(B2),B2,LEFT(B2,FIND(" ",B2)-1))

If the source cell isn't a number, it finds the first space and returns the characters before it. Technically, what it returns is a text value containing digits, but Excel is smart enough to treat it as a number in calculating the product (no need to nest that inside another function to convert it to a number). And note that if the number is floating point, this will still work.

Caveat: This relies on "clean" source data, i.e., either a numeric entry, or a text entry where it starts with a number which is followed by a space. If there is a cell that doesn't conform to that, you would need to either clean up the data or include some form of error checking. The best solution would be to split the two data columns so that any text is in a separate cell.

  • This is great, thanks! But the first non-numeric character might not be a space. I wonder if we can use some kind of pattern search in FIND function. – jeff Jun 18 '16 at 19:56
  • What kinds of characters, other than a space, could it be? – fixer1234 Jun 18 '16 at 19:58
  • Well I don't want to define a whitelist, so I'm looking for a wild search that just excludes numbers (and maybe . and ,). – jeff Jun 18 '16 at 20:00
  • That sure gets way more complicated than A2*B2. :-) I'll have to think about a way to incorporate regex. If there's too much existing data to manually clean up, you would want something along those lines to automate the process, anyway, to separate the non-numbers into another cell. If you ever need to explain the sheet to someone else, or maintain it later, A2*B2 is a lot easier than making sense of a long formula that's there just to handle the cell values. – fixer1234 Jun 18 '16 at 20:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.