Format Cell- Hope this makes sense

I have a spreadsheet that is used by multiple users. I need to see if there
is a way to separate floor numbers and room numbers.

Example:
In column "D" there will be:

5012-2
6004
7012-3
6004-02
ICUD
3014a

The floor number is the first number in the column. I need to see if this
can be separated to 5th floor, 6th floor, ICU (which may also be listed as
3014, 3012 etc. or ICU). I need to have a formula to count the number that
went to the 5th floor, 6th, etc.

I thought that if there is a formula to enter to separate the floors instead
of adding another column and having everyone enter the floor that would be
easier.

Hope this makes sense.

=SUMPRODUCT(--(LEFT(A1:A20,1)="5"))

counts those items starting with 5

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Denise" <Denise@discussions.microsoft.com> wrote in message
news:1A6AC316-BA91-480A-8CA0-79B622356053@microsoft.com...
> I have a spreadsheet that is used by multiple users. I need to see if
there
> is a way to separate floor numbers and room numbers.
>
> Example:
> In column "D" there will be:
>
> 5012-2
> 6004
> 7012-3
> 6004-02
> ICUD
> 3014a
>
> The floor number is the first number in the column. I need to see if this
> can be separated to 5th floor, 6th floor, ICU (which may also be listed as
> 3014, 3012 etc. or ICU). I need to have a formula to count the number
that
> went to the 5th floor, 6th, etc.
>
> I thought that if there is a formula to enter to separate the floors
instead
> of adding another column and having everyone enter the floor that would be
> easier.
>
> Hope this makes sense.