Жан задав запитання Excel на цьому тижні:
Я намагаюся перетворити книгу цін постачальника в Excel, щоб відповідати їх UPC-кодам нашій системі UPC-кодів. У їх прейскуранті є стовпець у Excel із серією цифр, наприклад. 000004560007 АБО cel000612004. Що мені потрібно зробити, це в два рази. Мені потрібно зняти перші три нулі, не видаляючи жодних інших 0 у клітинці. Далі я повинен додати 3-значний код "upc" перед першою цифровою цифрою в комірці. Це буде постійною потребою. Мені доведеться навчити кількох людей користуватися системою Excel.
Схоже на те, що Жан вже розглядав можливість редагування-заміни, щоб позбутися трьох нулів. Це не спрацює, оскільки заміна редагування на "000004560007" призведе до того, що обидва випадки 000 зникнуть.
Спочатку я хочу запропонувати Жану вставити тимчасовий стовпець поруч із поточними кодами цін, написати формулу для перетворення, а потім скористатися Edit-Copy, Edit-PasteSpecial-Values, щоб скопіювати формулу назад над вихідними кодами цін.
У цьому випадку погано задокументована функція REPLACE () зробить свою справу. Я був розчарований реалізацією REPLACE (). Я міг би подумати, що він вимагатиме конкретного тексту для заміни, але замість цього він просить користувача визначити позиції символів для заміни. REPLACE замінить частину текстового рядка новим рядком. Чотири аргументи, які ви передаєте функції, - це комірка, що містить старий текст, положення символу в старому тексті, який потрібно замінити, кількість символів, які потрібно замінити, та новий текст, який потрібно використовувати.
Моє спрощуюче припущення полягає в тому, що три нулі представляють початок UPC-коду в рядку. Таким чином, немає необхідності шукати перший числовий символ у комірці. Як тільки формула знайде три нулі, вона може замінити ці три нулі рядком "upc".
Щоб знайти місце першого входження "000" у тексті, скористайтеся цією формулою:
=FIND(A2,"000")
Формула, яку Жану потрібно було б ввести в клітинку B2, буде такою:
=REPLACE(A2,FIND("000",A2),3,"upc")
Моя думка полягає в тому, щоб аналітики ціноутворення виділили ряд клітинок, а потім викликали цей макрос. Макрос використовуватиме цикл із написом "Для кожної комірки у виділенні" на початку. За допомогою цього циклу "комірка" стає спеціальною змінною діапазону. Ви можете використовувати cell.address або cell.value або cell.row, щоб знайти адресу, значення або рядок поточної комірки в циклі. Ось макрос:
Public Sub Jean() ' This macro will replace the first occurrence of "000" ' in each selected cell with the string "upc" ' copyright 1999 www.MrExcel.com For Each cell In Selection cell.Value = Replace(cell.Value, "000", "upc", 1, 1, vbTextCompare) Next cell End SubЗверніть увагу, що це макрос руйнівного типу. Коли користувач виділяє діапазон комірок і запускає макрос, ці комірки будуть змінені. Само собою зрозуміло, що ви хочете ретельно протестувати свій макрос на тестових даних, перш ніж випустити його на реальні виробничі дані. Коли у вас є така ситуація, як у Жана, коли від вас постійно вимагатимуть перетворення стовпця за допомогою складної формули, написання простого макросу, подібного до наведеного тут, може стати ефективним та економічним часом інструментом.
