Extracting specific data from a cell
 

I know the answer to this is probably simple and obvious but I can't find
it. If I have a column of cells containing data that references imperial
weight. i.e. each cell contains a value such as 12st 10lbs or 11st 07 lbs. I
wish to convert this to kilograms using a calculation that converts this
data into lbs and then multiplies by 0.4536. How do I work with the first
number (i.e. stones) and then the second number (lbs).
Regards,
complete newbie and thickhead,
Neil

Extracting specific data from a cell
 

Instead of a monster formula, use Data TextToColumns, and select the
'space' as a delimter. That will create two columns. Insert a column
between those two. Then use TextToColumns again on column A, using 's' as a
delimiter, and on column C using 'l'. (This all assumes this to be a
one-time process; if not it would be worth creating the forumlas using
'find', 'left','mid' and 'value' that you can apply next time as well).
You'll wind up with the number of stones in column A and pounds less than a
stone in column C.
--Bruce

"Neil Bowen" wrote:

I know the answer to this is probably simple and obvious but I can't find
it. If I have a column of cells containing data that references imperial
weight. i.e. each cell contains a value such as 12st 10lbs or 11st 07 lbs. I
wish to convert this to kilograms using a calculation that converts this
data into lbs and then multiplies by 0.4536. How do I work with the first
number (i.e. stones) and then the second number (lbs).
Regards,
complete newbie and thickhead,
Neil

Extracting specific data from a cell
 

Bruce,
Many thanks,
Regards,
Neil
"bpeltzer" wrote in message
...
Instead of a monster formula, use Data TextToColumns, and select the
'space' as a delimter. That will create two columns. Insert a column
between those two. Then use TextToColumns again on column A, using 's' as
a
delimiter, and on column C using 'l'. (This all assumes this to be a
one-time process; if not it would be worth creating the forumlas using
'find', 'left','mid' and 'value' that you can apply next time as well).
You'll wind up with the number of stones in column A and pounds less than
a
stone in column C.
--Bruce

"Neil Bowen" wrote:

I know the answer to this is probably simple and obvious but I can't find
it. If I have a column of cells containing data that references imperial
weight. i.e. each cell contains a value such as 12st 10lbs or 11st 07
lbs. I
wish to convert this to kilograms using a calculation that converts this
data into lbs and then multiplies by 0.4536. How do I work with the first
number (i.e. stones) and then the second number (lbs).
Regards,
complete newbie and thickhead,
Neil

Extracting specific data from a cell
 

"Neil Bowen" wrote in message
...
I know the answer to this is probably simple and obvious but I can't find
it. If I have a column of cells containing data that references imperial
weight. i.e. each cell contains a value such as 12st 10lbs or 11st 07 lbs.
I wish to convert this to kilograms using a calculation that converts this
data into lbs and then multiplies by 0.4536. How do I work with the first
number (i.e. stones) and then the second number (lbs).
Regards,
complete newbie and thickhead,
Neil

Provided your data are always in the format of:
<any string <space <any number <lbs
this makes the conversion lbs to Kg:

=MID(A1,SEARCH(" ",A1,1),SEARCH("lbs",A1,1)-
SEARCH(" ",A1,1))*0.4536

Ciao
Bruno

Extracting specific data from a cell
 

On Sun, 30 Oct 2005 15:00:14 +0000 (UTC), "Neil Bowen"
wrote:

I know the answer to this is probably simple and obvious but I can't find
it. If I have a column of cells containing data that references imperial
weight. i.e. each cell contains a value such as 12st 10lbs or 11st 07 lbs. I
wish to convert this to kilograms using a calculation that converts this
data into lbs and then multiplies by 0.4536. How do I work with the first
number (i.e. stones) and then the second number (lbs).
Regards,
complete newbie and thickhead,
Neil


If your goal is to convert it to kg, the simplest equation would be to download
and install Longre's free add-in: morefunc.xll from http://xcell05.free.fr/

and then use the following formula:

=REGEX.MID(A1&" 0","\d+",1)*6.35029318+
REGEX.MID(A1&" 0","\d+",2)*0.453592309748811

Obviously you can shorten the conversion constants depending on your desired
precision.

The formula assumes your values are integers. If the pounds could be
fractions, then the formula would be:

=REGEX.MID(A1&" 0","\d+",1)*6.35029318+
REGEX.MID(A1&" 0","\d*\.?\d*",2)*0.453592309748811


--ron

Extracting specific data from a cell
 

Thanks Bruno
Neil
"Bruno Campanini" wrote in message
...
"Neil Bowen" wrote in message
...
I know the answer to this is probably simple and obvious but I can't find
it. If I have a column of cells containing data that references imperial
weight. i.e. each cell contains a value such as 12st 10lbs or 11st 07 lbs.
I wish to convert this to kilograms using a calculation that converts this
data into lbs and then multiplies by 0.4536. How do I work with the first
number (i.e. stones) and then the second number (lbs).
Regards,
complete newbie and thickhead,
Neil

Provided your data are always in the format of:
<any string <space <any number <lbs
this makes the conversion lbs to Kg:

=MID(A1,SEARCH(" ",A1,1),SEARCH("lbs",A1,1)-
SEARCH(" ",A1,1))*0.4536

Ciao
Bruno