Extracting specific data from a cell

[Neil Bowen]

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

 

[bpeltzer]

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]

Bruce,
Many thanks,
Regards,
Neil

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

 

[Bruno Campanini]

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

 

[Ron Rosenfeld]

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

 

[Neil Bowen]

Thanks Bruno
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