Глава 17 · 17.11
Извличане на улица, номер и допълнение към номера от една колона с адреси
Адресите от онлайн магазин, регистър на членове или стара клиентска база често се намират в една колона: „Dorpsstraat 12-2“. За етикети за пратки, пощенска кампания или импортиране при куриерска фирма улицата, номерът и допълнението трябва да са отделно. С Текст в колони това не става добре, защото имената на улиците сами съдържат интервали. Разделянето на име и фамилия е описано в Собствено и фамилно име от една колона разделяне; адресите са по-сложни. За тях Euro-Office предлага функциите REGEXEXTRACT и REGEXREPLACE, които работят с шаблон за търсене.
Като пример използваме списъка с клиенти на Bloemisterij Het Zonnetje, с данни в колона A:
- Koningin Julianalaan 15 a
- Dorpsstraat 12-2
- Laan 1940-1945 8B
- Kerkplein 7
- Hoofdstraat 120 bis
Стъпка 1: номерът с допълнението
Въведете в B2:
=REGEXEXTRACT(A2;"\d+\S*( ?[A-Za-z]{1,3})?$")
Шаблонът в кавичките означава: една или повече цифри (\d+), след това евентуално знаци без интервал (\S*, например -2 или B), след това евентуално интервал с 1 до 3 букви (например „ a“ или „ bis“), и всичко това в самия край на текста ($). Резултат: 15 a, 12-2, 8B, 7 и 120 bis. Годините в „Laan 1940-1945“ не се засягат, защото се взема предвид само последната част от адреса.
Стъпка 2: улицата
Въведете в C2:
=TRIM(REGEXREPLACE(A2;"\d+\S*( ?[A-Za-z]{1,3})?$";""))
REGEXREPLACE заменя същата част с нищо, а TRIM премахва интервала в края. Остават Koningin Julianalaan, Dorpsstraat, Laan 1940-1945 и т.н.
Стъпка 3: номерът и допълнението поотделно
Ако и номерът, и допълнението трябва да бъдат отделно, продължете с резултата от стъпка 1 в B2:
- Само номерът, в D2: =REGEXEXTRACT(B2;"^\d+") (цифрите в началото): 15, 12, 8, 7, 120.
- Допълнението, в E2: =TRIM(REGEXREPLACE(B2;"^\d+[- ]?";"")): a, 2, B, нищо, bis.
След това разпрострете формулите до последния ред. Или работете по колони: изберете например B2:B6, въведете формулата и натиснете Ctrl+Enter; така тя се въвежда наведнъж във всички избрани клетки.

Адрес със запетаи
Ако целият адрес е в една клетка, например „Dorpsstraat 12, 1234 AB Utrecht“, формулата =TEXTSPLIT(A9;", ") го разделя при запетаята: „Dorpsstraat 12“ остава в самата клетка, а „1234 AB Utrecht“ попада в съседната клетка. След това разделете пощенския код и населеното място с TEXTBEFORE и TEXTAFTER, които връщат текста преди или след n-тия разделител. Ако „1234 AB Utrecht“ е в C9:
- Пощенски код: =TEXTBEFORE(C9;" ";2) връща 1234 AB (всичко преди втория интервал).
- Населено място: =TEXTAFTER(C9;" ";2) връща Utrecht.
Готово? Превърнете ги в постоянни стойности
Разделените колони са формули, които се позовават на колона A. Ако искате да изтриете колона A или да изпратите файла на някого с по-стара версия на Excel, превърнете резултатите в обикновен текст: изберете колоните, копирайте ги с Ctrl+C и ги поставете на същото място с Ctrl+V. Под поставените клетки се появява бутонче (Ctrl). Щракнете върху него и под Стойност изберете Поставете само стойността.

Капани
- Изключенията остават: „Postbus 12“, „Hoofdstraat 3 hs“, „tegenover 14“. Поставете филтър върху колоните и проверете за празни или странни резултати, преди да използвате списъка.
- Главни букви: шаблонът [A-Za-z] обхваща малки и главни букви. Ако използвате само [a-z], 8B няма да бъде разпознат.
- #N/A при REGEXEXTRACT означава, че шаблонът не се среща, например при адрес без номер.
- Други електронни таблици: Excel поддържа тези REGEX функции само в последните версии на Microsoft 365. Колега с по-стара версия ще вижда #NAME?. В такъв случай първо превърнете резултатите в стойности.
- Може и без REGEX, но с ограничения: =TEXTAFTER(A2;" ";-1) връща всичко след последния интервал. Това работи за „Kerkplein 7“ и „Dorpsstraat 12-2“, но при „15 a“ получавате само a.
