Splitting a text string into string and number

I have set up a formula to read a cell with the value "a1" and split into two
parts "a" and "1" using the right and left functions, but when I try to
count the value (1) it sees it as text instead of an integer. Is there a
function that converts the string to a number? A1 means annual leave 1 hr. I
have other codes such as "S8" which is sick leave 8 hrs. I'm trying to create
a formula that calculates the differnt types of leave in a list of cells
which represent a calendar. I could do this with multiple columns, but I'm
trying to keep the information all in one cell, so it can be in a calendar
format.

Thanks.

=right(), =left(), =mid()
all return text

If you want to convert text that looks like a number, you could use:

=right(a1,1)+0
=right(a1,1)*1
=--right(a1,1)

All of them will coerce the text to number (and all will fail if that text
string doesn't look like a number!)




mcambrose wrote:
>
> I have set up a formula to read a cell with the value "a1" and split into two
> parts "a" and "1" using the right and left functions, but when I try to
> count the value (1) it sees it as text instead of an integer. Is there a
> function that converts the string to a number? A1 means annual leave 1 hr. I
> have other codes such as "S8" which is sick leave 8 hrs. I'm trying to create
> a formula that calculates the differnt types of leave in a list of cells
> which represent a calendar. I could do this with multiple columns, but I'm
> trying to keep the information all in one cell, so it can be in a calendar
> format.
>
> Thanks.

--

Dave Peterson

Dave, I tried using this solution in an array formula such as
=sum(right((a1:a4),1)*1)) and I still get #value. Is there something else
that willl work with array formulas or did I interpret your reply
incorrectly? In cells a1 to a4 would be the values a1, s1,s3,a8. I was hoping
to get the answer (1+1+3+8 = 13).

Thanks

"Dave Peterson" wrote:

> =right(), =left(), =mid()
> all return text
>
> If you want to convert text that looks like a number, you could use:
>
> =right(a1,1)+0
> =right(a1,1)*1
> =--right(a1,1)
>
> All of them will coerce the text to number (and all will fail if that text
> string doesn't look like a number!)
>
>
>
>
> mcambrose wrote:
> >
> > I have set up a formula to read a cell with the value "a1" and split into two
> > parts "a" and "1" using the right and left functions, but when I try to
> > count the value (1) it sees it as text instead of an integer. Is there a
> > function that converts the string to a number? A1 means annual leave 1 hr. I
> > have other codes such as "S8" which is sick leave 8 hrs. I'm trying to create
> > a formula that calculates the differnt types of leave in a list of cells
> > which represent a calendar. I could do this with multiple columns, but I'm
> > trying to keep the information all in one cell, so it can be in a calendar
> > format.
> >
> > Thanks.
>
> --
>
> Dave Peterson
>

A formula like =sum(right((a1:a4),1)*1)) is an *array* formula.
Double-click on the cell to edit. Instead of Enter press
Shift+Ctrl+Enter.

HTH
Kostis Vezerides

On Tue, 21 Feb 2006 07:24:27 -0800, mcambrose
<[email protected]> wrote:

>Dave, I tried using this solution in an array formula such as
>=sum(right((a1:a4),1)*1)) and I still get #value. Is there something else
>that willl work with array formulas or did I interpret your reply
>incorrectly? In cells a1 to a4 would be the values a1, s1,s3,a8. I was hoping
>to get the answer (1+1+3+8 = 13).
>
>Thanks

If you got a #VALUE! error, it is likely that one of the cells in the range
does not have a number as the rightmost character.


--ron