Easy question

[ryan.fitzpatrick3]

How do I eliminate text out of this 2.5 OZ , i just want the 2.5
to show or whatever number is in there regardless of how much digits
it is. I would prefer to have this in the query field and not vba
please. Thank you.

Ryan

 

[ryan.fitzpatrick3]

I tried this Pack Size: Left([size_desc],4), but if
the number is 10 digits long it doesn't work obviously. Thank you.

 

[Jim Cone]

Find the position of the space and extract all characters to the left of it.
=LEFT(D5,FIND(" ",D5,1)-1)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


<[email protected]>
wrote in message
I tried this Pack Size: Left([size_desc],4), but if
the number is 10 digits long it doesn't work obviously. Thank you.

 

[ryan.fitzpatrick3]

It says "undefined function 'FIND' in expression.


ryan

Find the position of the space and extract all characters to the left of it.
=LEFT(D5,FIND(" ",D5,1)-1)
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

<[email protected]>
wrote in message
I tried this Pack Size: Left([size_desc],4), but if
the number is 10 digits long it doesn't work obviously. Thank you.

How do I eliminate text out of this 2.5 OZ , i just want the 2.5
to show or whatever number is in there regardless of how much digits
it is. I would prefer to have this in the query field and not vba
please. Thank you.

Ryan

 

[Conan Kelly]

Ryan Fitzpatrick,

FYI: It looks like you are asking a question about MS Access........this is
an Excel programming/VBA newsgroup.

Is the text you are trying to eliminate from every record always going to be
" OZ"?

If so, this should do it:

Pack Size: Left([size_desc],Len([size_desc])-3)

That will always remove the last 3 characters, but it might return text. If
you need to use it in a calculation, you might have to convert it to some
numerical data type.

If the number of characters you need to remove varies, we might be able to
get it to work, but will need more info: what are all possible values of
text you want to remove?

HTH,

Conan

I tried this Pack Size: Left([size_desc],4), but if
the number is 10 digits long it doesn't work obviously. Thank you.

How do I eliminate text out of this 2.5 OZ , i just want the 2.5
to show or whatever number is in there regardless of how much digits
it is. I would prefer to have this in the query field and not vba
please. Thank you.

Ryan

 

[Jim Cone]

The expression I provided is a worksheet formula that
should be entered in a worksheet cell.
Also, cell D5 should contain "2.5 OZ" (or similar text).
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


<[email protected]>
wrote in message
It says "undefined function 'FIND' in expression.
ryan

Find the position of the space and extract all characters to the left of it.
=LEFT(D5,FIND(" ",D5,1)-1)
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


<[email protected]>
wrote in message
I tried this Pack Size: Left([size_desc],4), but if
the number is 10 digits long it doesn't work obviously. Thank you.

How do I eliminate text out of this 2.5 OZ , i just want the 2.5
to show or whatever number is in there regardless of how much digits
it is. I would prefer to have this in the query field and not vba
please. Thank you.

Ryan

 

[Nigel]

If it Excel you are talking about take the Val() of the text string e.g.

Val(2.5 OZ) returns 2.5

 

[ryan.fitzpatrick3]

Thank you guys, worked great.

If it Excel you are talking about take the Val() of the text string e.g.

Val(2.5 OZ) returns 2.5

--

Regards,
Nigel
(e-mail address removed)