Допустим, у вас есть рабочий лист с множеством кодов продуктов в столбце A.

Эти коды имеют формат A4, B12, AD4 и т. Д. В связи с изменением способа работы вашей компании вам предлагается изменить все коды продуктов, чтобы они содержали дефис между буквами и цифрами.

Есть несколько способов выполнить эту задачу. Если структура ваших кодов продуктов согласована, то вставить тире совсем несложно.

Например, если всегда будет одна буква, за которой следуют цифры, вы можете использовать такую ​​формулу:

=LEFT(A1,1) & "-" & RIGHT(A1,LEN(A1)-1)

Скорее всего, ваши данные не будут структурированы, то есть у вас может быть одна или две буквы, за которыми следуют до трех цифр. Таким образом, и A4, и QD284 будут действительными кодами продукта. В этом случае формула решения требует немного больше творчества.

Один из способов справиться с этим — использовать формулу массива. Рассмотрим следующую формулу:

=REPLACE(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1)),0),0,"-")

Если значения находятся в A1-A10, вы можете поместить эту формулу в B1, а затем скопировать ее в столбец. Поскольку это формула массива, ее необходимо вводить, нажимая Ctrl + Shift + Enter. Формула находит расположение первого числа в ячейке и вставляет перед ним дефис.

Предположим, для примера, что ячейка A1 содержит BR27. Самая внутренняя часть формулы, INDIRECT («1: 100»), преобразует текст 1: 100 в диапазон. Это используется для того, чтобы вставка или удаление строк не влияла на формулу. Следующая часть формулы, ROW (INDIRECT («1: 100»)), по существу создает массив значений 1-100: 1,2,3, …​, 99,100. Это используется для воздействия на каждого персонажа в ячейке.

Следующая часть, MID (A1, ROW (INDIRECT («1: 100»)), 1), относится к каждому отдельному символу в строке. В результате получается массив: «B», «R», «2» и «7». Умножение массива на 1 (следующая часть формулы)

приводит к преобразованию каждого из отдельных символов в число. Если символ не является числом, это преобразование дает ошибку. В случае преобразования строки (BR27) это приводит к: #VALUE, #VALUE, 2 и 7.

Следующим шагом является применение функции ЕСТЬ ОШИБКА к результатам умножения. Это преобразует ошибки в ИСТИНУ, а не ошибки в ЛОЖЬ, давая ИСТИНА, ИСТИНА, ЛОЖЬ и ЛОЖЬ. Функция ПОИСКПОЗ ищет в массиве значений ИСТИНА и ЛОЖЬ точное совпадение ЛОЖЬ. В этом примере функция ПОИСКПОЗ возвращает число 3, поскольку первое значение ЛОЖЬ находится в третьей позиции массива. На данный момент мы, по сути, знаем расположение первого числа в ячейке.

Последняя функция — REPLACE, которая используется для вставки тире в исходную строку, начиная с третьего символа.

Как вы понимаете, расшифровывать формулу для выполнения преобразования может быть непросто. Тем, кто так склонен, может быть проще просто создать пользовательскую функцию. Следующий макрос является примером макроса, который возвращает строку с тире в нужном месте:

Function DashIn(myText As String)

Dim i As Integer     Dim myCharCode As Integer     Dim myLength As Integer

Application.Volatile     myLength = Len(myText)

For i = 1 To myLength         myCharCode = Asc(Mid(myText, i, 1))

If myCharCode >= 48 And myCharCode <= 57 Then             Exit For         End If     Next i     If i = 1 Or i > myLength Then         DashIn = myText     Else         DashIn = Left(myText, i - 1) & "-" _           & Mid(myText, i, myLength - 1)

End If End Function

Макрос проверяет каждый символ в исходной строке и, когда находит первый числовой символ, вставляет дефис в этой точке. Вы могли бы использовать функцию следующим образом:

=DashIn(A1)

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (2613) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и более поздних версий) здесь:

link: / excelribbon-Inserting_Dashes_between_Letters_and_Numbers [Вставка тире между буквами и цифрами].