Остановите Excel от преобразования ваших данных в научную нотацию при импорте данных из файла CSV или TXT.
Смотреть видео
- У вас есть пробелы, которые TRIM не удаляет
- У вас есть номер детали, заканчивающийся на e и цифру
- У вас есть номер детали, состоящий более чем из 15 цифр
- Если вы импортируете файл в формате CSV, номера деталей меняются на научную нотацию.
- Как показать расширения в проводнике Windows
- Если вы импортируете, открыв файл .txt, вы можете попытаться указать, что эти столбцы являются текстовыми, но
- при нахождении / замене неразрывного пробела (символ 160) номера деталей меняются на научное представление
- Решение - использовать данные, получить внешние данные, из текста.
- Однако эта команда отсутствует в Office 365, и ее заменила команда «Получить и преобразовать».
- Если у вас нет From Text, щелкните правой кнопкой мыши панель быстрого доступа и выберите Customize
- В верхнем левом раскрывающемся списке измените на Все команды. Найти из текста (устаревшая версия) и добавить в QAT
- Вы можете открыть файл CSV, используя From Text, и это позволит вам пройти через мастер импорта текста
- На шаге 2 мастера укажите запятую и alt = "" + 0160 как пользовательские. Считайте последовательные разделители одним целым.
- Спасибо Яну Карелу: здесь
- Не забудьте проголосовать: здесь
Стенограмма видео
Изучите Excel из подкаста, эпизод 2087: Запретить научную нотацию при импорте
Вау! Сегодня мы займемся множеством разных вопросов. Итак, несколько человек присылают похожие задачи. Либо у нас есть номер детали - смотрите этот номер детали прямо здесь, где находится вторая до последней цифры. Это все числа, но вторая последняя цифра - это буква: D, E, F. Эти E будут проблемой. Эти буквы E будут представлены в виде научных обозначений, или любой номер детали, полностью состоящий из более чем 15 цифр, будет иметь проблемы.
Кроме того, многие люди спрашивали меня о получении или получении данных из этой веб-системы, и есть пробелы до и после этого, которые не подходят для TRIM. Так что, если у вас есть какая-либо из этих трех проблем, потенциально я потенциально помогу вам.
Хорошо, теперь первое, что мы здесь сделаем, это взглянем на этот CSV-файл, хорошо? И я просто дважды щелкну, чтобы открыть это; и поскольку это файл CSV, они не удосуживаются проводить нас через мастер импорта текста, что ужасно, не так ли? Итак, вы видите, что у нас есть проблемы. Во-первых, из-за букв E все, что содержит E, вошло в научную нотацию. И если мы попытаемся это исправить, вернемся к числу или что-то в этом роде. Мы проиграли. Мы потеряли вещи. То же самое и с вещами, длина которых превышает 16 символов, даже если вы снова измените их на числа, у вас будут проблемы, потому что вы потеряли последние несколько цифр, хорошо. Это просто ужасно.
А это - О, это здорово! Это круто. Посмотрите на это, да, оно пришло без преобразования. О, но есть начальные и конечные пробелы, наши ВПР не будут работать. Хорошо, теперь первое, что мы хотим сделать, это выяснить, что это за начальные и конечные пробелы, потому что, если я попытаюсь = обрезать = обрезать эту вещь, она никуда не денется. И как я могу сказать, что он никуда не денется? Потому что я могу соединить звездочку = «» перед и … И вы видите, что там еще что-то есть, хорошо? И когда это происходит, там еще что-то есть. Вы знаете, почему TRIM не делает - TRIM должен избавляться от начальных и конечных пробелов. Итак, вот что я делаю. Что касается = CODE ЛЕВОГО этого, 1, чтобы увидеть, что это такое, и это символ 160. О, это не то, что мы должны получить.На этот раз там обычное старое место, просто пространство. Нажмите пробел, это символ 32. Это настоящее пространство, от которого TRIM избавляется. Что это за 160? 160 - это неразрывное пространство. Это действительно популярно среди веб-сайтов, потому что если вы создаете веб-страницу, вы оставляете пространство. Что ж, Internet Explorer и Chrome просто собираются сделать это единым пространством. Но если вы поставите неразрывные пробелы, 3 из них, то фактически сохранятся 3 пробела.Но если вы поставите неразрывные пробелы, 3 из них, то фактически сохранятся 3 пробела.Но если вы поставите неразрывные пробелы, 3 из них, то фактически сохранятся 3 пробела.
Хорошо, теперь мы должны сделать одну неприятную вещь. Чтобы избавиться от этих 160 пробелов, вам нужно либо ввести символ 160, что означает, что у вас должна быть цифровая клавиатура. Хорошо, теперь обратите внимание, как я это делаю. Я собираюсь удерживать клавишу alt = "", а теперь с цифровой клавиатурой 0160 отпустить, и вот оно. Видишь, это только что появилось, хорошо? Теперь, если вам повезло, что у вас есть цифровая клавиатура, тогда эта проблема будет Ctrl + H, в Find what, удерживая Alt + 0160, отпустите и замените ничем, замените все. Сделано 34 замены. Но я буду сыном пистолета, они изменили эти числа на научную запись, хорошо. Так что я смог их ввести, но у меня все еще есть шанс перейти на научную нотацию.
Кстати, если у вас нет цифровой клавиатуры, поэтому вы можете набрать Alt + 0160, использование цифр в верхней части не сработает. Забудь об этом, никогда не получится. Поэтому, если вам отчаянно нужно было ввести символ 160 = CHAR (160), не нажимайте Enter, нажмите F9, который оценит это. Хорошо, теперь в этой ячейке у меня есть один пробел, но это не символ 32, это символ 160, и я собираюсь удерживать клавишу Shift и нажимать клавишу со стрелкой влево, чтобы выбрать его. Ctrl + C, теперь это в моем буфере обмена. Теперь мы пойдем сюда. Выберите эти два столбца, Ctrl + H, Найдите что: я вставлю туда Ctrl + V. Заменить на: ничего. Заменить все, нажмите ОК, нажмите Закрыть. И снова, я взлетаю себе под нос, потому что они преобразовали все это в научную нотацию.
Хорошо, теперь я обычно говорю людям, что они должны сделать, то, что я обычно говорю людям сделать, - это вернуться в проводник Windows и преобразовать это из файла CSV в файл .txt. Я не могу этого увидеть. Если вы этого не сделаете - если вы не видите расширения, нажмите клавишу alt = "", а затем "Инструменты", а затем "Параметры папки" и прямо здесь, в разделе "Просмотр", где написано Скрыть расширения для известных типов файлов, снимите флажок. Это худшая обстановка на свете. Я все время это выключаю. Я хочу видеть расширение таким образом, я могу щелкнуть правой кнопкой мыши, переименовать и изменить его на .txt. Хорошо, а какая польза от доступа к .txt? Ой, это круто. Когда я делаю .txt, потому что тогда, если я перейду в «Файл» и «Открыть», мы перейдем к этой папке. И открываю версию .txt, нажимаю ОК. Хорошо, эй,Я должен пройти и сказать на каждом этапе, что это за тип, и поэтому я могу сказать - А, давайте разберем его запятыми. Ага, красиво. И далее, и прямо здесь я хочу сказать, не лажайте с этим. Текст - это способ сказать, не лажайте с этим. Здесь то же самое, не лажайте с этим. Вот эти, не лажайте с ними: Текст, Текст, Текст. И обычно нам не нравится использовать текст, но здесь, где они меняют мои числа, использование текста позволит им войти, и они не будут научными обозначениями. Вау! Это потрясающе. Именно так я всегда предлагал решить эту проблему, но потом я увидел эту замечательную статью моего друга, Яна Карела, JKP Application Development Services, которая показала мне новый блестящий способ. Блестящий новый способ. Итак, позвольте мне показать вам это. Я'Я помещу ссылку на эту статью в комментарии на YouTube. Обязательно ознакомьтесь со статьей.
Хорошо, поэтому мы собираемся вернуться сюда, и замечательно то, что нам не нужно переименовывать это из текста - из CSV в текст, потому что он будет иметь дело с CSV, что действительно хорошо, потому что если мы получим этот файл каждый божий день мы хотим иметь дело с CSV. А вот и безумная вещь. Если вы используете Excel 2013 или более раннюю версию, мы хотим перейти на вкладку «Данные», «Получить внешние данные» и использовать «Из текста». Но если вы используете Office 365, последнюю версию Office 365, этого раздела нет. Хорошо, поэтому в Office 365 щелкните здесь правой кнопкой мыши и скажите «Настроить панель быстрого доступа», а слева выберите «Все команды».
Это действительно длинный список, мы собираемся перейти к буквам F. F для From Text - посмотрите на все эти From, мне нужно найти тот, который говорит From Text (Legacy). Это старая версия. Теперь видите, они хотят, чтобы мы использовали Power Query, но давайте просто создадим что-то, что будет работать для всех. Теперь, когда у меня это есть, теперь, когда у меня есть From Text Legacy, я просто перейду к Brand New Worksheet, Insert Worksheet. Теперь у нас есть место для перехода от текста, и мы перейдем к нашему CSV-файлу. Нажмите Импорт, и мы скажем с разделителями. Да! Но на шаге 2 я скажу, что хочу разделить его запятой. Я также хочу разграничить его в пространстве, и я хотел разделить его на Alt + 0160. Опять же, если у вас нет цифровой клавиатуры, выНам придется использовать трюк, который я показал вам пару минут назад, чтобы скопировать его и вставить в эту ячейку. И ах! Между прочим, если вы поставили несколько элементов рядом друг с другом, даже запятую и Alt + 0160, то относитесь к этим последовательным разделителям как к одному. Хорошо, этот текст, фактически все это, будет текстом. Мы не хотим, чтобы они облажались ни с одним из них. Они все остаются такими.
Now, here's the beautiful thing. First off, CSV files, will get the answer to these questions because we use From Text and we get to say where we're going to put it and Properties, that we want to Save the query definition. And then, every time we open this file maybe we can go out and refresh the data, so this workbook could be the holder that every time we open this, it's going to go back out to the CSV and remember all of our answers and do that- do all the steps. So click Close, click OK and nothing comes in and scientific notation it's all been changed to Text. And you know, we don’t have to worry about just double-clicking they’ll be CSV file because it's allowing us to specify what each of those fields are.
Alright, my book, Power Excel with, has a lot of different tips: 617 Excel mystery solved. Now this one, unfortunately, is not in the book but it'll be in the next edition of the book, I guarantee that.
A lot of different things we talked about today. If you have spaces that TRIM won’t remove, learn how to figure this out. If you have a part number that ends in E and then a single digit or you have a partner with more than 15 digits, when you import a CSV file all of those are going to change to scientific notation. And oh by the way, if you've been burned by this, here's another URL I'm going to paste down in YouTube videos. Come out here and vote to make the Excel team try and tell them Excel team that you want Excel to stop changing large numbers to scientific notation. 584 votes right now. Let's try and get that up to 600, 700, 800 or even a thousand.
Alright, so I showed you how to change the extensions in Windows Explorer. When you open a .txt file, yeah, sure the things coming as text but as soon as you try and get rid of those non-breaking spaces, the part numbers change back to scientific notation and you have to swear again. So we use Data, Get External Data From Text, the old legacy version. If missing from Office 365 replaced by Get & Transform, so you have to right-click the Quick Access Toolbar and Customize in order to find it. Now when we open a CSV file with From Text, it lets you go through the text import wizard which is better than just double-clicking the CSV file. And step 2, the wizard will specify a comma and a space and then Alt+0160 as custom. Treat consecutive delimiters as one.
Это потрясающий трюк от моего друга Яна Карела, и не забудьте проголосовать на excel.uservoice.com. Что ж, я хочу поблагодарить вас за то, что заглянули. Увидимся в следующий раз на другой сетевой трансляции от.
Скачать файл
Загрузите образец файла здесь: Podcast2087.xlsm
