Find the difference between two alphanumeric cells

[titchestiny]

Hi,

I'm trying to find the difference between two alphanumeric cells. For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]C[/TD]
[TD]K[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1AB[/TD]
[TD]2BC[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2AB[/TD]
[TD]4BC+[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2AB[/TD]
[TD]5BC[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

Conditions:
- The columns that I am comparing are not next to each other, nor do they start at A1.
- The letters in the cell do not need to be used.
- The answer needs to return an absolute value.
- The numbers always appear to the left of the letter string.

I've tried to use the IF, AND and FIND functions, and I was successful in returning a result for one row, but the formula wouldn't work for the remaining rows of data.

Any help you can give, would be most appreciated.

Thanks,

Tt

p.s. I'm using excel 2010

 

[vogel997]

Maybe this helps?
[TABLE="width: 295"]
<tbody>[TR]
[TD]=LEFT(K3,1)-LEFT(C3,1)[/TD]
[/TR]
[TR]
[TD]=LEFT(K4,1)-LEFT(C4,1)[/TD]
[/TR]
[TR]
[TD]=LEFT(K5,1)-LEFT(C5,1)[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[Rick Rothstein]

Hi,

I'm trying to find the difference between two alphanumeric cells. For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]C[/TD]
[TD]K[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1AB[/TD]
[TD]2BC[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2AB[/TD]
[TD]4BC+[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2AB[/TD]
[TD]5BC[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

Conditions:
- The columns that I am comparing are not next to each other, nor do they start at A1.
- The letters in the cell do not need to be used.
- The answer needs to return an absolute value.
- The numbers always appear to the left of the letter string.

You gave us very good information, but you forgot to include one detail.. what is the biggest number that can be in a cell? Assuming 999, give this formula a try..

=LOOKUP(999,--LEFT(C3,{1,2,3}))-LOOKUP(999,--LEFT(E3,{1,2,3}))

If you maximum number is different, you can adjust the red highlighted parts to account for the difference... you need as many 9's in the two 999 parts as there are digits in your maximum value and the two comma delimited lists must count from 1 to the number of digits in your maximum value. So, if your maximum value were, say, 50000, then the formula would be adjusted to this...

=LOOKUP(99999,--LEFT(C3,{1,2,3,4,5}))-LOOKUP(99999,--LEFT(E3,{1,2,3,4,5}))

If there will only be single digits in front of the text, then use the formula vogel997 posted above.

 

[titchestiny]

Hi Vogel997,

It worked a treat.

Thanks for your help,

Tt

 

[titchestiny]

Hi Rick,

Thanks for your post. Apologies that I left a piece of key information out. The data only includes a single digit, so I have used vogel997's formula.

I appreciate the time you took to post. I never considered using the LOOKUP formula to solve the problem.

Thanks for the help,

Tt

 

[vogel997]

Interesting formula.
How would you separate the letters from the numbers if the length varies? Lookup as well?

 

[vibinmvenugopal]

This is really helping me as well. A quick question Rich, I have some negative values as well for eg -1AC or -40C. How do I deal with this.

A B
-40c 40c

How do I find the difference for this?

Thanks a ton for the help,
Vibin.

 

[XOR LX]

Hi,

What would be your expected result for:

C3: -1AC
E3: -40C

Regards

 

[mfexcel]

just to remind, as you mentioned you require absolute value, don't forget to be wrap the formula with =ABS() just in case the number in the 2nd column is less than that in 1st column...

Maybe this helps?
[TABLE="width: 295"]
<tbody>[TR]
[TD]=LEFT(K3,1)-LEFT(C3,1)[/TD]
[/TR]
[TR]
[TD]=LEFT(K4,1)-LEFT(C4,1)[/TD]
[/TR]
[TR]
[TD]=LEFT(K5,1)-LEFT(C5,1)[/TD]
[/TR]
</tbody>[/TABLE]