# Thread: Difference in numbers (XP/2003)

1. ## Difference in numbers (XP/2003)

I have a column of alpha-numeric number that look like this:
__A_______B___
A 110
A 125
A 200
B 100
B 130
There is always a single letter or symbol such as # at the beginning and a space
after that. The number can be three or four in length. I would like to setup a column B
with a formula to show the differences in the numbers.
For example in the first two A 125 – A 110 the difference is 15. The next is 75.
When the first character changes (i.e. A to [img]/forums/images/smilies/cool.gif[/img] it would not count. or equal zero.
Anyone have a formula that will work?

2. ## Re: Difference in numbers (XP/2003)

Assuming that the values begin in A1, enter the following formula in B2:
<code>
=IF(LEFT(A2,1)=LEFT(A1,1),VALUE(MID(A2,3,100))-VALUE(MID(A1,3,100)),"")
</code>
Fill down as far as the data in column A go.
If you'd rather have 0 than a blank when the first character changes, replace <code>""</code> at the end of the formula with 0.

3. ## Re: Difference in numbers (XP/2003)

Hi Richard

This can be done with a hidden or intermediary column.

In the corresponding cell in Column B type =RIGHT(A2,LEN(A2)-FIND(" ",A2)), This will select the numerical value in the string assuming that the numerical value extends past 999 and there is a preceding space as you decribe.

In the corresponding cell to the value in Column C (starting C2) type =IF(B2-B3<0,-(B2-B3),B2-B3)

This will provide the difference. I attach a workbook to describe further

4. ## Re: Difference in numbers (XP/2003)

Thank you Hans - it works great as always.

One quick questions in the - - VALUE(MID(A2,3,100))-VALUE(MID(A1,3,100) - - what does the 100 accomplish?

5. ## Re: Difference in numbers (XP/2003)

Thank you Jezza.
This also works just fine. The only difference is one additional step.
Many options will expand your vision.

6. ## Re: Difference in numbers (XP/2003)

The MID function has 3 arguments:
string - the text value from which to extract a portion.
start - the position where you want to start extracting (1 = at the beginning of the text).
length = number of characters you want to extract; it's OK to specify more characters than there are.

Since the number part is everything from the third position to the end, I used 100 as an arbitrary value (much) larger than the longest string you expect to encounter. I might have used 20 or 1000 as well.

7. ## Re: Difference in numbers (XP/2003)

BTW, Excel is rather forgiving when performing calculations - if you subtract two string values, it'll convert them to numbers before subtracting. So the formula can be simplified to
<code>
=IF(LEFT(A2,1)=LEFT(A1,1),MID(A2,3,100)-MID(A1,3,100),"")</code>

8. ## Re: Difference in numbers (XP/2003)

I was thinking that 1000 would be better since they do go into the "A 1000" and up level.
I do not expect they would exceed "A 9999".

Will try the second formula to see if any difference.

Thanks for the speedy response.

9. ## Re: Difference in numbers (XP/2003)

The 100 stands for the number of characters, not the value.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•