У меня есть такая строка:
"page_age:15#resp_gender:4#EN01CTY:3#QMktSize:6#Page_Device_usagesm:8"
и в Excel хотите получить результат, который отформатирован в столбцы, как это:
Resp_gender En01cty Page_Device_usage
4 3 8
Хорошо, если сделать это только для одной строки, T2C (текст в столбцы) + C & TP (копирование и транспонирование вставки) будет достаточно. Просто делаю каждый шаг 2 раза так:
T2C с # разделителем изменится
page_age:15#resp_gender:4#EN01CTY:3#QMktSize:6#Page_Device_usagesm:8
в
A | B | C | D | E
page_age:15 | resp_gender:4 | EN01CTY:3 | QMktSize:6 | Page_Device_usagesm:8
C & TP изменит его на строки
page_age:15
resp_gender:4
EN01CTY:3
QMktSize:6
Page_Device_usagesm:8
следующий T2C с разделителем : изменит его на почти готовую таблицу
A | B
page_age | 15
resp_gender | 4
EN01CTY | 3
QMktSize | 6
Page_Device_usagesm | 8
последний C & TP переключит его на вид, который мы хотим
A | B | C | D | E
page_age | resp_gender | EN01CTY | QMktSize | Page_Device_usagesm
15 | 4 | 3 | 6 | 8
Тем не менее, сделать это для нескольких строк будет много работы. Давайте пока предположим, что мы ищем только определенные значения, имеющие эти значения:
page_age:15#resp_gender:4#EN01CTY:3#QMktSize:6#Page_Device_usagesm:8
page_age:3#resp_gender:11#EN01CTY:9#QMktSize:4#Page_Device_usagesm:1
page_age:9#resp_gender:5#EN01CTY:7#QMktSize:2#Page_Device_usagesm:12
page_age:22#resp_gender:8#EN01CTY:1#QMktSize:5#Page_Device_usagesm:2
(имея это в ячейках A2 до A4 потому что мы хотим строку 1 для заголовков)
Что мы знаем? Перед значениями всегда стоит символ : а за ними - знак # (кроме последнего элемента).
Что мы хотим? Вырежьте текст перед и после значения, которое мы желаем.
Предположим снова что-то: мы хотим получить значения от resp_gender , поэтому мы устанавливаем resp_gender качестве заголовка для столбца B (в B1).
Начинаем резать все перед нашей ценностью. Таким образом, нам нужно найти, где это начинается. Функция FIND сделает именно это. Поэтому мы пробуем это с =FIND(B$1,$A1) ($ - для того, чтобы сделать его перетаскиваемым позже), и получаем 13. Просто поместите это в RIGHT формулу с LEN чтобы проверить это:
=RIGHT($A1,LEN($A1)-FIND(B$1,$A1))
приведет нас к esp_gender:4#EN01CTY:3#QMktSize:6#Page_Device_usagesm:8 .
Хорошо, нам нужно также вычесть длину самого заголовка, чтобы он стал:
=RIGHT($A2,LEN($A2)-FIND(B$1,$A2)-LEN($B1))
и мы получаем 4#EN01CTY:3#QMktSize:6#Page_Device_usagesm:8 . Круто, нет необходимости вычислять : потому что оно уже вырезано. Все, что нам нужно сделать сейчас, это вырезать текст после значения.
Чтобы не убивать формулу и держать ее короткой на данный момент, мы оставляем ее в B2, как есть, и делаем правильное сокращение в C2 (только сейчас). Как мы знаем, мы можем довольно легко найти # с помощью FIND . Слияние с LEFT формулой должно дать нам то, что мы хотим:
=LEFT(B2,FIND("#",B2))
дает нам 4# . ОК, один символ слишком длинный, так что только -1 сделает это:
=LEFT(B2,FIND("#",B2)-1)
Теперь мы объединяем обе формулы друг с другом, просто копируя формулу из B2 (без =) и вставляя ее в каждую B2 во второй формуле. Таким образом, мы получаем:
=LEFT(RIGHT($A2,LEN($A2)-FIND(B$1,$A2)-LEN($B1)),FIND("#",RIGHT($A2,LEN($A2)
-FIND(B$1,$A2)-LEN($B1)))-1)
который возвращает 4 . Похоже, это работает. Проверяя его для всех заголовков, мы получаем проблему, если мы идем к последней части (за которой не следует #). Но простой IFERROR покроет это. Итак, мы заканчиваем с этим:
=IFERROR(LEFT(RIGHT($A2,LEN($A2)-FIND(B$1,$A2)-LEN($B1)),FIND("#",RIGHT($A2,LEN($A2)
-FIND(B$1,$A2)-LEN($B1)))-1),RIGHT($A2,LEN($A2)-FIND(B$1,$A2)-LEN($B1)))
Это было много текста, чтобы получить формулу, которую вы хотите. Но просто возьмите это за небольшой урок, чтобы получить формулы в будущем (всегда имейте в виду, что есть разные способы получить именно это).