Separting negative numbers from letters in cells
 

Okay I had to code data and possible values were as follows:
-2c
-2b
-2a
99
1a
1b
2

I accidently put them all in the same cell however I need the letters and numbers in different cells (for example given |-2c| I would want to output |-2|c| with the straight lines being cells). I know there were other discussions on this issue, however, mine deals with actually keeping the negative numbers so those formulas wouldn't work. Thanks in advance for your help. FYI I am running Excel 2010 if that matters.

Separting negative numbers from letters in cells
 

Hi,

Am Wed, 15 May 2013 21:43:57 +0100 schrieb patatwork:

Okay I had to code data and possible values were as follows:
-2c
-2b
-2a
99
1a
1b
2

your values in column A. Then in B1:
=IF(ISTEXT(A1),--(LEFT(A1,LEN(A1)-1)),A1)
and in C1:
=IF(ISTEXT(A1),RIGHT(A1,1),"")
You can copy column B:C and pastespecial paste values and then delete
column A


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thanks! That was great!

Ignoring specific number
 

Hi,

Am Thu, 6 Jun 2013 19:29:11 +0100 schrieb patatwork:

Okay I am try to sum up half a row and subtract away the other half of a
row. It is a coding scheme however and I want excel to ignore every time
there is a 99, so pretend it is a 0 in other words, but there is too
much data to change them all to 0. Thanks in advance for your help.

try:
=SUMIF(A1:H1,"<99",A1:H1)-SUMIF(I1:P1,"<99",I1:P1)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2