Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
|
|||
|
|||
Thanks! That was great!
Quote:
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count only Letters in cells that contain both letters and numbers | Excel Worksheet Functions | |||
Formatting cells for letters and numbers | Excel Worksheet Functions | |||
change 2000 cells (negative numbers) into positive numbers | Excel Worksheet Functions | |||
Sorting - cells containing numbers, numbers and letters | Excel Discussion (Misc queries) | |||
Finding Numbers with Cells that also contain letters | Excel Discussion (Misc queries) |