Alpha & Numeric

Posted by Brian on December 09, 2001 12:34 PM

Is there a way where I can input into a cell a value like 8 R or 4 NA and still be able to add the 8 & 4?

Brian

Posted by Aladin Akyurek on December 09, 2001 12:46 PM

Brian --

No idea why you'd want to do is, but, if your entries follow the regular sysntax of your example, that is, numeric-part followed by a space followed by alpha-part, you can do:

=LEFT(A1,SEARCH(" ",A1))+LEFT(B1,SEARCH(" ",B1))

to add the numeric parts.

Aladin

Posted by Brian on December 09, 2001 1:50 PM

Aladin,

Thanks very much. This is for an overtime spreadsheet and the alpha would denote a refusal, acceptance, etc. and instead of making two columns, I can get it down to one.
Again thanks....Brian

Posted by Brian on December 09, 2001 1:59 PM

Aladin,

Excel says I've created a circular reference

Posted by Aladin Akyurek on December 09, 2001 2:08 PM

The formula is adding up 2 different cells and is meant itself to be yet in other cell like

A1 -> 4 N
B1 -> 8 H

and the formula say in C1.

Perhaps I misunderstood you.

Aladin

Posted by Brian on December 09, 2001 2:09 PM

Aladin, my mistake, it works.

Posted by Brian on December 09, 2001 2:27 PM

Aladin,

No it's my mistake. The formula works fine, it's doing what I asked for. However, I've tried doing a range, instead of adding A1 and B1, I'd like it to add A1:A12, etc. I'm new to this as you can tell.

Posted by Aladin Akyurek on December 09, 2001 2:55 PM

Brian --

Not sure I should give you the solution you're looking for. The formula that follows is powerful but also costly in terms of processing time. Here is the formula:

=SUMPRODUCT((LEFT(A1:A12,SEARCH(" ",A1:A12)-1)+0))

Please rethink before going down this path.

Cheers,

Aladin

Posted by Brian on December 09, 2001 3:14 PM

Thanks

Thanks Aladin