Help: flexible handling of units of measure (inches, cm, meters)

[jordiejones]

Hey all, I have a formula that splits a persons height from its unit of measure (in,cm,m) it’s intention is to be flexible to quickly capture the metric from different data sources. When I started working on if I used IFS but some of our data entry people are on older versions of Excel. Is there a way to say IF( A2 is only “m” then A1, then IF SEARCH A2 finds *in* CONVERT(A1,”in”,”m”) then IF SEARCH A2 finds *cm* CONVERT(A1,”cm”,”m”).

the problem I ran into is I can figure out how to get it to differentiate between the JUST “m” vs “*cm*. Any help would be greatly appreaciated.

 

[Eric W]

You could search for cm before you search for m, something like:

=CONVERT(A1,INDEX({"cm","in","m"},MATCH(1,COUNTIF(A2,{"*cm*","*in*","*m*"}),0)),"m")

 

[jordiejones]

**** that is clean. Wouldn’t have thought of an index and match to solve that. Great solution!

 

[Eric W]

Glad it works for you!

? ☃ ?