Как мне преобразовать данные по высоте в нескольких форматах в дюймы?

Question

У меня есть колонка с несколькими тысячами высот в разных форматах, которую я хотел бы преобразовать в дюймы.

Примеры из реальных данных:

5 футов 2 дюйма

6 футов 3 дюйма

6 футов 3 дюйма

6 футов 3 "

5 * 8

5,3

5'10"

5 71/2

Я думаю, что есть какой-то способ получить ноги в одном столбце и дюймы в секунду, чтобы вычислить дюймы. Ноги кажутся легкими, с ЛЕВОЙ, но я не уверен, как надежно получить дюймы в другой колонке.

Какой лучший способ сделать это?

Answer 1

Это будет многошаговое решение, и я бы решил, разбив его на части.
Я буду использовать ваш список, разделенный точкой с запятой (из комментариев), плюс одно дополнительное значение: 5 футов 7 1/2 ".

1. Стандартизируйте список настолько, насколько это возможно, используя ЗАМЕНУ:

   Col B: Replace "feet" with "ft"   =SUBSTITUTE(A2,"feet","ft")
   Col C: Replace "'" with "ft"      =SUBSTITUTE(B2,"'","ft")
   Col D: Replace "ft" and " ft" with " ft"
                                     =SUBSTITUTE(SUBSTITUTE(C2,"ft"," ft"),"  ft"," ft")
   Col E: Remove "inches"            =SUBSTITUTE(D2,"inches","")
   Col F: Remove "in"                =SUBSTITUTE(E2,"in","")
   Col G: Remove "                   =SUBSTITUTE(F2,CHAR(34),"")
   (CHAR(34) is the double quotes symbol, ")
   
   This can also be done with a long nested formula. I prefer simple.
   

2. Используйте ПОИСК, чтобы найти положение "фут"; если его там нет, напишите "0"

   Col H: =IFERROR(SEARCH(" ft",G2,1),0)
   

3. Подсчитайте количество футов

   Col I: =IFERROR(VALUE(LEFT(G2,H2-1)),0)
   

4. Дюймы сложнее, потому что они могут содержать дроби.
   Определите текст, представляющий дюймы и обрежьте начальные пробелы, используя TRIM.

   Col J: =IFERROR(MID(G2,H2+2,1000)),0)
   Col K: =TRIM(J2)
   

5. Определите, есть ли дроби в тексте "дюймы".
   Если есть смешанная дробь, целые числа должны быть отделены от дроби пробелом (71/2 = 35,5; 7 1/2 = 7,5)
   Найдите положение любых пробелов и символов "/" после них.

   Col L: =IFERROR(SEARCH(" ",K2,1),0)
   Col M: =IFERROR(SEARCH("/",K2,L2+1),0)
   

6. Найдите количество дюймов или (если есть смешанная дробь) общее количество дюймов.
   Если текст дюймов = "", число дюймов равно 0.
   Если символа «/» нет, предположим, что это десятичное число.
   Если есть символ "/", все число - это число перед пробелом.

   Col N: =IF(K2="",0,IF(M2=0,VALUE(K2),IFERROR(VALUE(LEFT(K2,L2-1)),0)))
   

7. Если есть символ «/», рассчитайте числитель и знаменатель.
   Если дроби нет, знаменатель = 1.

   Col O: Numerator    =IFERROR(VALUE(IF(M2=0,0,MID(K2,L2+1,M2-L2-1))),0)
   Col P: Denominator  =IFERROR(VALUE(IF(M2=0,1,MID(K2,M2+1,100))),1)
   

8. Добавить список номеров

   Col Q: =I2*12+N2+O2/P2
   

Answer 2

Вы можете использовать MID или Right для дюймов, или вы можете конвертировать текст в столбец. Также есть преобразование формул, которое можно использовать после разделения данных, эта формула может преобразовывать все виды единиц, см. Справку по ней.