Обрабатываем выгрузку в виде текста

Слушательница одного из потоков моего онлайн-курса обратилась за советом: как оптимальным образом решить задачу с обработкой выгрузки текстовых данных.

Данные разделены запятыми и выглядят так:

38226866310,"e8aba7eb45e88f28b2f9f43f","2017-06-23 19:43:05.338203",543134

при этом:

• количество знаков в первом наборе цифр варьируется от 11 до 19
• количество знаков в последнем наборе абсолютно произвольное, при этом в копейках (а нам на выходе нужно разделить на 100, чтобы получить в рублях)
• количество знаков во втором и третьем значении постоянное, но третье значение нужно извлечь не полностью - только дату и время, без долей секунды.

Конечно, в Таблицах есть инструмент "Разделить на колонки" (как "Текст по столбцам" в Excel):

И с помощью него можно разделить данные:

Но все равно придется обрабатывать третье и четвертое значения (из третьего убирать доли секунды менять формат на дату - пока это просто текст; четвертое делить на 100).

Поэтому проще воспользоваться формулами. Достаточно их один раз в файле прописать, а дальше можно будет вставлять выгрузку и получать результат.

Для первого и второго значения все довольно просто. Есть функция SPLIT, делающая то же, что и "Разделить на колонки", но она формирует виртуальный массив, и из нее можно достать только один из элементов по номеру - с помощью ИНДЕКСа:

=ИНДЕКС(SPLIT($A3;",");1)

Эта формула выдает первый элемент из текста, разбитого по разделителю "запятая".

С первым значением этого достаточно. Во втором остаются ненужные кавычки - удалим их с помощью функции ПОДСТАВИТЬ:

=ПОДСТАВИТЬ(ИНДЕКС(SPLIT($A3;",");2);СИМВОЛ(34);"")

Вместо СИМВОЛ (34) подставляем ничего, то есть удаляем. СИМВОЛ(34) - это и есть кавычки. Напрямую их нельзя ввести в формуле. Они являются служебным символом.

Третий элемент - дату и время - достанем с помощью регулярных выражений. Мы уже писали о функции REGEXEXTRACT и делали видеоурок.

Она извлекает из текста фрагмент, соответствующий регулярному выражению.

В данном случае регулярное выражение будет выглядеть так:

\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}

то есть текст, соответствующий шаблону

####-##-## ##:##:##, где # - любая цифра (\d - это цифра в рег. выражениях, {4} - сколько раз она повторяется).

Получим дату и время, но пока - в формате текста, а не даты и времени, поскольку извлекли это из текстовой ячейки (и функции REGEX возвращают текст):

Но есть функция ЗНАЧЕН, конвертирующая текст в число:

Упс! это потому, что у ячейки автоматический формат. Дата и время - это числа (дата - целое, по кол-ву дней с 1 января 1900 года, а время - дробная часть).

Поменяем формат на дату и время:

Готово.

Осталось четвертое значение - берем четвертый элемент функции SPLIT и делим на 100 (чтобы получить рубли вместо копеек):

Результат:

Ссылка на файл