Splitting a text string into string and number

[Guest]

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]

=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!)

 

[Guest]

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

 

[vezerid]

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

 

[Ron Rosenfeld]

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