Treat "Text" as a Number?
 

Excel2003

Col D contains text such as ... 15/3 ... 1/4 ... 8/14 ... etc ... with 1 to
3 numerics occuring on either side of the "/".

In a conditional Formula I need to treat any "text" that looks like a number
on the left side of the "/" as a number.

I was using ... Sum(left(D5,1) ... as part of my formula & this was working
great until I encountered the 2 & 3 characters on the left side of the "/".
Now I am stuck.

Thanks ... Kha

Treat "Text" as a Number?
 

You can use =search() to look for the position of the slash:

=sum(--(left(d5,search("/",d5),-1),...)

=left() returns text that =sum() will ignore (unless you do some other
arithmetic to it). The -- stuff coerces the text to a real number.



Ken wrote:

Excel2003

Col D contains text such as ... 15/3 ... 1/4 ... 8/14 ... etc ... with 1 to
3 numerics occuring on either side of the "/".

In a conditional Formula I need to treat any "text" that looks like a number
on the left side of the "/" as a number.

I was using ... Sum(left(D5,1) ... as part of my formula & this was working
great until I encountered the 2 & 3 characters on the left side of the "/".
Now I am stuck.

Thanks ... Kha

--

Dave Peterson

Treat "Text" as a Number?
 

Typo alert... an extra comma crept into your formula in front of the -1.

Ken, Dave meant to post this...

=SUM(--LEFT(D5,SEARCH("/",D5)-1),...)

Rick


"Dave Peterson" wrote in message
...
You can use =search() to look for the position of the slash:

=sum(--(left(d5,search("/",d5),-1),...)

=left() returns text that =sum() will ignore (unless you do some other
arithmetic to it). The -- stuff coerces the text to a real number.



Ken wrote:

Excel2003

Col D contains text such as ... 15/3 ... 1/4 ... 8/14 ... etc ... with 1
to
3 numerics occuring on either side of the "/".

In a conditional Formula I need to treat any "text" that looks like a
number
on the left side of the "/" as a number.

I was using ... Sum(left(D5,1) ... as part of my formula & this was
working
great until I encountered the 2 & 3 characters on the left side of the
"/".
Now I am stuck.

Thanks ... Kha

--

Dave Peterson

Treat "Text" as a Number?
 

Thanks for the correction Rick.



"Rick Rothstein (MVP - VB)" wrote:

Typo alert... an extra comma crept into your formula in front of the -1.

Ken, Dave meant to post this...

=SUM(--LEFT(D5,SEARCH("/",D5)-1),...)

Rick

"Dave Peterson" wrote in message
...
You can use =search() to look for the position of the slash:

=sum(--(left(d5,search("/",d5),-1),...)

=left() returns text that =sum() will ignore (unless you do some other
arithmetic to it). The -- stuff coerces the text to a real number.



Ken wrote:

Excel2003

Col D contains text such as ... 15/3 ... 1/4 ... 8/14 ... etc ... with 1
to
3 numerics occuring on either side of the "/".

In a conditional Formula I need to treat any "text" that looks like a
number
on the left side of the "/" as a number.

I was using ... Sum(left(D5,1) ... as part of my formula & this was
working
great until I encountered the 2 & 3 characters on the left side of the
"/".
Now I am stuck.

Thanks ... Kha

--

Dave Peterson

--

Dave Peterson

Treat "Text" as a Number?
 

Dave / Rick ... (Good morning)

FYI ... Perfect ... Works as requested ...

Thank you for supporting these boards ... Kha

"Dave Peterson" wrote:

Thanks for the correction Rick.



"Rick Rothstein (MVP - VB)" wrote:

Typo alert... an extra comma crept into your formula in front of the -1.

Ken, Dave meant to post this...

=SUM(--LEFT(D5,SEARCH("/",D5)-1),...)

Rick

"Dave Peterson" wrote in message
...
You can use =search() to look for the position of the slash:

=sum(--(left(d5,search("/",d5),-1),...)

=left() returns text that =sum() will ignore (unless you do some other
arithmetic to it). The -- stuff coerces the text to a real number.



Ken wrote:

Excel2003

Col D contains text such as ... 15/3 ... 1/4 ... 8/14 ... etc ... with 1
to
3 numerics occuring on either side of the "/".

In a conditional Formula I need to treat any "text" that looks like a
number
on the left side of the "/" as a number.

I was using ... Sum(left(D5,1) ... as part of my formula & this was
working
great until I encountered the 2 & 3 characters on the left side of the
"/".
Now I am stuck.

Thanks ... Kha

--

Dave Peterson

--

Dave Peterson