|  | Change height (5'8") to inches (68) | | |
| | | Russ3Z |  |
|  Posted: Mon Sep 15, 2008 9:11 am Post subject: Change height (5'8") to inches (68) |  |
As the subject says, I am looking for a way to convert a column of
heights, expressed with the ' and " symbols for feet and inches,
respectively, into just a number of inches. If possible I would like
to do this without using a macro. So the following:
6'2"
5'3"
5'8"
would become
74
63
68
Any help is most appreciated. Thank you. |
| |
| | | Niek Otten | |
| | Posted: Mon Sep 15, 2008 9:11 am Post subject: Re: Change height (5'8") to inches (68) | |
=LEFT(A1,FIND("'",A1)-1)*12+MID(A1,FIND("'",A1)+1,LEN(A1)-FIND("'",A1)-1)
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"Russ3Z" <russ3z@yahoo.com> wrote in message news:844236bb-ea1d-44d9-b7c8-eb323e19326e@w7g2000hsa.googlegroups.com...
| As the subject says, I am looking for a way to convert a column of
| heights, expressed with the ' and " symbols for feet and inches,
| respectively, into just a number of inches. If possible I would like
| to do this without using a macro. So the following:
|
| 6'2"
| 5'3"
| 5'8"
|
| would become
|
| 74
| 63
| 68
|
| Any help is most appreciated. Thank you. |
| |
| | | Guest | |
| | Posted: Mon Sep 15, 2008 10:10 am Post subject: Re: Change height (5'8") to inches (68) | |
Maybe...
=(MID(A1,1,FIND("'",A1)-1)*12)+MID(A1,FIND("'",A1)+1,FIND("""",A1)-
FIND("'",A1)-1)
On Sep 15, 10:58 am, "Niek Otten" <nicol...@xs4all.nl> wrote:
| Quote: | =LEFT(A1,FIND("'",A1)-1)*12+MID(A1,FIND("'",A1)+1,LEN(A1)-FIND("'",A1)-1)
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"Russ3Z" <rus...@yahoo.com> wrote in messagenews:844236bb-ea1d-44d9-b7c8-eb323e19326e@w7g2000hsa.googlegroups.com...
| As the subject says, I am looking for a way to convert a column of
| heights, expressed with the ' and " symbols for feet and inches,
| respectively, into just a number of inches. If possible I would like
| to do this without using a macro. So the following:
|
| 6'2"
| 5'3"
| 5'8"
|
| would become
|
| 74
| 63
| 68
|
| Any help is most appreciated. Thank you. |
|
| |
| | | Ron Rosenfeld | |
| | Posted: Mon Sep 15, 2008 10:10 am Post subject: Re: Change height (5'8") to inches (68) | |
On Mon, 15 Sep 2008 02:11:45 -0700 (PDT), Russ3Z <russ3z@yahoo.com> wrote:
| Quote: | As the subject says, I am looking for a way to convert a column of
heights, expressed with the ' and " symbols for feet and inches,
respectively, into just a number of inches. If possible I would like
to do this without using a macro. So the following:
6'2"
5'3"
5'8"
would become
74
63
68
Any help is most appreciated. Thank you.
|
=REPLACE(A1,FIND("'",A1),5,"")*12+SUBSTITUTE(MID(A1,FIND("'",A1)+1,5),"""","")
--ron |
| |
| | | Mais qui est Paul ? | |
| | Posted: Mon Sep 15, 2008 2:51 pm Post subject: Re: Change height (5'8") to inches (68) | |
Bonsour® Russ3Z avec ferveur ;o))) vous nous disiez :
| Quote: | As the subject says, I am looking for a way to convert a column of
heights, expressed with the ' and " symbols for feet and inches,
respectively, into just a number of inches. If possible I would like
to do this without using a macro. So the following:
6'2"
5'3"
5'8"
would become
74
63
68
|
=CONVERT(SUBSTITUTE(SUBSTITUTE(A1,CHAR(34),"/12"),CHAR(39)," "),"ft","in")
HTH
--
--
@+
;o))) |
| |
|
|
