how can I subtract excel cell values that contain both a number a.
 

I have measurement data from an ATE system where the results are given as
both a number and 'unit' letters for example 22.3 dB, 2.109 MHz, 12.06 V and
0.175 A. To calculate temperature drift from these readings, I need to
subtract cells that contain both a number and a letter i.e.

2.012 MHz - 2.005 MHz = 0.007
does not matter if the answer contains the 'units' or not.

anyone know how I can achieve this ?

Answer: how can I subtract excel cell values that contain both a number a.
 

Yes, you can achieve this by using the following steps:

1. Select the cell where you want to display the result of the subtraction.
2. Type the formula
   Code:

   ---

   =VALUE(cell1)-VALUE(cell2)

   ---

   where "cell1" and "cell2" are the cells containing the values you want to subtract.
3. Press Enter to calculate the result.

The VALUE function in Excel converts a text string that represents a number to a numeric value. By using this function, you can extract the numeric value from the cells that contain both a number and a letter.

For example, if you want to subtract the values in cells A1 and B1, you would type in the cell where you want to display the result.

After you press Enter, Excel will calculate the result of the subtraction and display it in the selected cell. If you want to display the result with the units, you can simply type them after the formula in the same cell.

John,

Assuming there is a space between the value and the suffix, you could strip
the numeric value out of a cell this:

=LEFT(M30,FIND(" ",M30))

This will return all the characters in the string in M30 up to the space -
this should be your numbers. You could then use this idea to subtract the
numeric elements of two cells from each other thus:

=LEFT(M30,FIND(" ",M30))-LEFT(M31,FIND(" ",M31))

Hope this helps - unledd there isn't a space between the number and the
suffix, in which case I'll have to think again!

Cheers

Pete







"John Sayaff" wrote:

I have measurement data from an ATE system where the results are given as
both a number and 'unit' letters for example 22.3 dB, 2.109 MHz, 12.06 V and
0.175 A. To calculate temperature drift from these readings, I need to
subtract cells that contain both a number and a letter i.e.

2.012 MHz - 2.005 MHz = 0.007
does not matter if the answer contains the 'units' or not.

anyone know how I can achieve this ?

John,

my last answer, just to be sure, you need to subtract 1 when you've
found your space - thuis, if the space occurs at the fourth position, you
only want to see the first 3 characters, and so on. This version also adds
the appropriate suffix onto the end of your formula. Wish I'd thought of it
first time round!

=LEFT(M30,FIND(" ",M30)-1)-LEFT(M31,FIND(" ",M31)-1)&" Mhz"

Pete


"John Sayaff" wrote:

I have measurement data from an ATE system where the results are given as
both a number and 'unit' letters for example 22.3 dB, 2.109 MHz, 12.06 V and
0.175 A. To calculate temperature drift from these readings, I need to
subtract cells that contain both a number and a letter i.e.

2.012 MHz - 2.005 MHz = 0.007
does not matter if the answer contains the 'units' or not.

anyone know how I can achieve this ?

John,

This was a GOOD problem!

This final version strips the suffix from M30 and adds it, prefixed by a
space, to your answer.

=LEFT(M30,FIND(" ",M30)-1)-LEFT(M31,FIND(" ",M31)-1)&"
"&RIGHT(M30,LEN(M30)-FIND(" ",M30))

I'll go away now.

Pete



"John Sayaff" wrote:

I have measurement data from an ATE system where the results are given as
both a number and 'unit' letters for example 22.3 dB, 2.109 MHz, 12.06 V and
0.175 A. To calculate temperature drift from these readings, I need to
subtract cells that contain both a number and a letter i.e.

2.012 MHz - 2.005 MHz = 0.007
does not matter if the answer contains the 'units' or not.

anyone know how I can achieve this ?