Преобразование данных - Мир MS Excel

Регистрация

Напомнить пароль

= Мир MS Excel/Преобразование данных - Мир MS Excel

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Преобразование данных

Преобразование данных

Дата: Среда, 07.09.2011, 20:00 | Сообщение № 1

Группа: Пользователи

Ранг: Участник

Сообщений: 82

Репутация: 0 ±

Замечаний: 0% ±

Доброго времени суток уважаемые форумчане!
Прошу помощи!
Суть вопроса в ледующем: Есть таблица в которой есть один из столбцов данные в котором записаны в следующем виде:
51012012555
51012010323
51012008888
51012000024
51012000489
и т.д.
Необходимо преобразовать эти данные в этом столбце в следующий вид:
051-012-012555
051-012-010323
051-012-008888
051-012-000024
051-012-000489
и т.д.
возможно ли это без использования разбиения по столбцам, добавления нулей и объединения по новой?
Желательно сделать это в макросе!
Поиском нашел несколько тем - но не то что нужно!
Заранее спасибо за ответ!

Ответить

Дата: Среда, 07.09.2011, 20:11 | Сообщение № 2

Группа: Друзья

Ранг: Участник клуба

Сообщений: 3690

Репутация: 790 ±

Замечаний: 0% ±

365

Вам нужно, чтобы это так выглядело, или сами данные преобразовать?
А то есть такой вариант - см.файл

К сообщению приложен файл: CustomFormat.xls (17.5 Kb)

webmoney: E265281470651 Z422237915069
USDT TRC20: TN8XeEF17o5KPBD9pNwYzNyruycuAc2mVD

Ответить

Дата: Среда, 07.09.2011, 20:37 | Сообщение № 3

Группа: Друзья

Ранг: Экселист

Сообщений: 5660

Репутация: 1163 ±

Замечаний: 0% ±

2010

Для ячейки в соседнем столбце справа

Code

ActiveCell = Replace(Replace(ActiveCell.Offset(, -1), Left(ActiveCell.Offset(, -1), 2), "0" & Left(ActiveCell.Offset(, -1), 2) & "-"), _
Mid(Replace(ActiveCell.Offset(, -1), Left(ActiveCell.Offset(, -1), 2), "0" & Left(ActiveCell.Offset(, -1), 2) & "-"), 5, 3), _
Mid(Replace(ActiveCell.Offset(, -1), Left(ActiveCell.Offset(, -1), 2), "0" & Left(ActiveCell.Offset(, -1), 2) & "-"), 5, 3) & "-")

Или на месте

Code

ActiveCell = Replace(Replace(ActiveCell, Left(ActiveCell, 2), "0" & Left(ActiveCell, 2) & "-"), _
Mid(Replace(ActiveCell, Left(ActiveCell, 2), "0" & Left(ActiveCell, 2) & "-"), 5, 3), _
Mid(Replace(ActiveCell, Left(ActiveCell, 2), "0" & Left(ActiveCell, 2) & "-"), 5, 3) & "-")

А если страшно, можно так

Code

Sub bbb()
a = ActiveCell
b = Replace(a, Left(a, 2), "0" & Left(a, 2) & "-")
ActiveCell = Replace(b, Mid(b, 5, 3), Mid(b, 5, 3) & "-")
End Sub

Быть или не быть, вот в чем загвоздка!

Сообщение отредактировал RAN - Среда, 07.09.2011, 20:45

Ответить

Формуляр

Дата: Среда, 07.09.2011, 20:46 | Сообщение № 4

Группа: Друзья

Ранг: Ветеран

Сообщений: 832

Репутация: 255 ±

Замечаний: 0% ±

Excel 2003, 2013

А можно одной формулой:

Code

= TEXT(A1,"000-000-000000")

К сообщению приложен файл: NumFrmt.xls (13.5 Kb)

Excel 2003 EN, 2013 EN

Ответить

Дата: Среда, 07.09.2011, 20:57 | Сообщение № 5

Группа: Авторы

Ранг: Старожил

Сообщений: 1613

Репутация: 563 ±

Замечаний: 0% ±

Excel 2013, 2016

Quote (RAN)

А если страшно...

если не очень страшно smile

smile

Code

Dim s$
s = ActiveCell.Previous.Text
ActiveCell = "0" & Mid(s, 1, 2) & "-" & Mid(s, 3, 3) & "-" & Mid(s, 6)

Яндекс.Деньги 4100159601573

Ответить

Дата: Среда, 07.09.2011, 21:08 | Сообщение № 6

Группа: Друзья

Ранг: Экселист

Сообщений: 5660

Репутация: 1163 ±

Замечаний: 0% ±

2010

Или

Code

Sub bb(): ActiveCell = Format(ActiveCell, "000-000-000000"): End Sub

biggrin

Быть или не быть, вот в чем загвоздка!

Ответить

Дата: Среда, 07.09.2011, 21:09 | Сообщение № 7

Группа: Админы

Ранг: Местный житель

Сообщений: 16714

Репутация: 6503 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Если формулой, то можно так:

Code

=ТЕКСТ(A1;"000-000-000000")

Уже было выше
а макросом тако вариант для нескольких ячеек (предварительно выделить):

Code

Sub Макрос1()
c_ = ActiveCell.SpecialCells(xlLastCell).Column + 1
Selection.Offset(, c_).FormulaR1C1 = "=TEXT(RC[-" & c_ & "],""000-000-000000"")"
Selection = Selection.Offset(, c_).Value
Selection.Offset(, c_).Clear
End Sub

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Дата: Среда, 07.09.2011, 23:38 | Сообщение № 8

Группа: Пользователи

Ранг: Участник

Сообщений: 82

Репутация: 0 ±

Замечаний: 0% ±

Большое спасибо всем отклинувшимся! Даже не ожидал smile

smile

Hugo, - нет - нужно чтобы преобразовались сами данные - а не так - чтобы они выглядели.
nilem - при использовании Вашего варианта получаеться что-то не то.
RAN, - Ваш вариант - где три примера очень подходит - а не подскажите - как сделать - чтоб выполнить для ячеек с имеющимися данными по этому столбцу - а не только для активной?
Формуляр, _Boroda_, - с формулами хорошо - но данный лист постоянно меняться - поэтому с формулами не очень удобно - но спасибо за него - теперь буду знать - что так можно сделать!
_Boroda_, Ваш с вариант с макросом не сработал - написал Variable not defined sad

sad

Сообщение отредактировал Ed_Vard - Среда, 07.09.2011, 23:47

Ответить

Дата: Среда, 07.09.2011, 23:54 | Сообщение № 9

Группа: Друзья

Ранг: Участник клуба

Сообщений: 3690

Репутация: 790 ±

Замечаний: 0% ±

365

Если Variable not defined - значит напишите выше
Dim c as long

Видишь, _Boroda_ - нужно всёж Option Explicit ставить smile

smile

webmoney: E265281470651 Z422237915069
USDT TRC20: TN8XeEF17o5KPBD9pNwYzNyruycuAc2mVD

Ответить

Дата: Четверг, 08.09.2011, 00:06 | Сообщение № 10

Группа: Друзья

Ранг: Экселист

Сообщений: 5660

Репутация: 1163 ±

Замечаний: 0% ±

2010

Выделяем нужное и давим, давим... smile

smile

К сообщению приложен файл: 7150195.xls (36.5 Kb)

Быть или не быть, вот в чем загвоздка!

Сообщение отредактировал RAN - Четверг, 08.09.2011, 00:26

Ответить

Дата: Четверг, 08.09.2011, 00:12 | Сообщение № 11

Группа: Пользователи

Ранг: Участник

Сообщений: 82

Репутация: 0 ±

Замечаний: 0% ±

Quote (RAN)

Выделяем нужное и давим, давим... smile
К сообщению приложен файл: 7150195.xls(36Kb)

В Вашем варианте визуально данные меняються - но в ячейке остаються прежними - изменение формата не спасает!
У меня в дальнейшем из этого листа идет выборка макросом значений. Это поле являеться ключевым для поиска - т.е. мне нужны именно данные а не визуальное отображение!
Ваш вариант в посте с тремя примера это делает!

Сообщение отредактировал Ed_Vard - Четверг, 08.09.2011, 00:13

Ответить

Дата: Четверг, 08.09.2011, 00:20 | Сообщение № 12

Группа: Пользователи

Ранг: Участник

Сообщений: 82

Репутация: 0 ±

Замечаний: 0% ±

Hugo спасибо - Ваша подсказка + макрос от _Boroda_ - получаеться именно то что нужно! Огромное спасибо!

Ответить

Дата: Четверг, 08.09.2011, 00:27 | Сообщение № 13

Группа: Друзья

Ранг: Экселист

Сообщений: 5660

Репутация: 1163 ±

Замечаний: 0% ±

2010

Хотел без цикла. Не получилось.
Перезалил файл.

Внутрь цикла можете подставить любой из вариантов, заменив activecell на cl.

Быть или не быть, вот в чем загвоздка!

Сообщение отредактировал RAN - Четверг, 08.09.2011, 00:40

Ответить

Дата: Вторник, 04.10.2011, 19:46 | Сообщение № 14

Группа: Пользователи

Ранг: Участник

Сообщений: 82

Репутация: 0 ±

Замечаний: 0% ±

Доброго времени суток Всем форумчанам smile

smile

Поскольку вопрос из той же темы - что и ранее - не стал создавать новую, а продолжу в этой! Прошу помощи в следующем вопросе:
У меня есть столбец с данными следующего вида:
156-649-695 22
064-330-215-21
037-550-639 60
032-460-798-38
037-550-650-55
031-037-891-16
и т.д. вниз на 30000 строк. Мне неоходимо привести его в соответствие т.е. данные должны быть такими
156-649-695 22
064-330-215 21
037-550-639 60
032-460-798 38
037-550-650 55
031-037-891 16
т.е. после 11 символа убрать если есть тире и вставить пробел.
Вот макрос - которым пользуюсь:

Code

Sub xxx()
a = ActiveCell
ActiveCell = Replace(a, Right(a, 3), " " & Right(a, 2))
End Sub

Все вроде бы нормально - но попадаються, причем довольно часто значения - в которых помимо нужного еще что нибудь как напрмер значение - выделенное красным плучаеться вот таким 037 550-650 55 - что не есть правильно.
Поэтому и вопрос - может я неправильно вычисляю это тире и нужно как то подругому это делать?
Заранее прошу сильно не пинать sad

sad

Ответить

Дата: Вторник, 04.10.2011, 21:20 | Сообщение № 15

Группа: Друзья

Ранг: Экселист

Сообщений: 5660

Репутация: 1163 ±

Замечаний: 0% ±

2010

Code

ActiveCell = Left(ActiveCell, 11) & " " & Right(ActiveCell, 2)

Быть или не быть, вот в чем загвоздка!

Ответить

Дата: Вторник, 04.10.2011, 22:07 | Сообщение № 16

Группа: Авторы

Ранг: Старожил

Сообщений: 1613

Репутация: 563 ±

Замечаний: 0% ±

Excel 2013, 2016

Вариант для примера:

Code

Sub ert()
Dim x, i&
x = [a1:a6].Value
For i = 1 To 6
Mid$(x(i, 1), 12, 1) = " "
Next i
[a1:a6].Value = x
End Sub

Яндекс.Деньги 4100159601573

Ответить

Дата: Среда, 26.10.2011, 15:39 | Сообщение № 17

Группа: Пользователи

Ранг: Участник

Сообщений: 82

Репутация: 0 ±

Замечаний: 0% ±

RAN, nilem, _Boroda_, Большое спасибо! Временно был отлучен от интернета по техническим причинам - поэтому не получилось поблагодарить сразу sad

sad

Взял за основу вариант nilem для большего удобства!

Ответить

Преобразование данных

Страница 1 из 1
1

© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!