Say I have 5'2", how could I have a function to turn that into 5.167. I need a formula because I have about 5,000 rows to convert.
Thanks.
MacBook Pro 13", OS X 10.11
The only thing I can think of is that the occurrence should be omitted... try this instead:
=SUBSTITUTE(SUBSTITUTE(B2,D2&"_",""),D2&"_","")
thank you! how can i make this where I can copy and paste the formula down the column so it does it for all 5,000 rows?
Thanks!!!!!
to fill down, select cells B2 thru F2, copy
select cells B2 thru the end of column F, paste
thanks for some reason formula E2 is not working?
can you post the formula you are using and a screenshot of what's happening?
and also this:
I think the syntax error is generated because your screenshot shows you have an extra = at the beginning of the formula in E2. In the formula editor remove that extra = and all should be well.
SG
good catch SG. I was tired in my last reply
This worked!!! thank you! figured it all out! i so appreciate all of your help in this! finally finishing this up lol!
it wasn't the "=" issue.
assuming the values for feet and inches are in column A and you want the corresponding decimal feet to be in column B, you could do the following:
select cell B2, then type, or copy and paste from here, the formula:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, CHAR(34), ""), CHAR(8220), ""), CHAR(8221), ""), CHAR(8217), "_"), CHAR(8216), "_"), CHAR(39), "_")
shorthand for this is:
B2=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, CHAR(34), ""), CHAR(8220), ""), CHAR(8221), ""), CHAR(8217), "_"), CHAR(8216), "_"), CHAR(39), "_")
C2=SEARCH("_", B2, 1)
D2=LEFT(A2, C2−1)
E2=SUBSTITUTE(SUBSTITUTE(B2,D2&"_","",occurrence),D2&"_","",occurrence)
F2=D2+E2÷12
to fill down, select cells B2 thru F2, copy
select cells B2 thru the end of column F, paste
this assumes that the format of the entries in column A is:
F'I"
where F = number of feet
and
I = number of inches
the reason for the long formulas is there are variations of single and double quotes that can cause trouble.
the double quote comes in three forms:
1) simple ascii
2) fancy unicode open double quote
3) fancy unicode close double quote
same thing for the single quote mark
How to convert feet & inches to a decimal?
