Extracting specific data from a cell

  • Thread starter Thread starter Neil Bowen
  • Start date Start date
N

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
 
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
 
Bruce,
Many thanks,
Regards,
Neil
bpeltzer said:
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
Click to expand...
 
Neil Bowen said:
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
Click to expand...

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
 
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
Click to expand...

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
 
Thanks Bruno
Neil
Bruno Campanini said:
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
Click to expand...
 
Back
Top