Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

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

 

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

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Преобразование данных
Преобразование данных
Ed_Vard Дата: Среда, 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
и т.д.
возможно ли это без использования разбиения по столбцам, добавления нулей и объединения по новой?
Желательно сделать это в макросе!
Поиском нашел несколько тем - но не то что нужно!
Заранее спасибо за ответ!
 
Ответить
СообщениеДоброго времени суток уважаемые форумчане!
Прошу помощи!
Суть вопроса в ледующем: Есть таблица в которой есть один из столбцов данные в котором записаны в следующем виде:
51012012555
51012010323
51012008888
51012000024
51012000489
и т.д.
Необходимо преобразовать эти данные в этом столбце в следующий вид:
051-012-012555
051-012-010323
051-012-008888
051-012-000024
051-012-000489
и т.д.
возможно ли это без использования разбиения по столбцам, добавления нулей и объединения по новой?
Желательно сделать это в макросе!
Поиском нашел несколько тем - но не то что нужно!
Заранее спасибо за ответ!

Автор - Ed_Vard
Дата добавления - 07.09.2011 в 20:00
Hugo Дата: Среда, 07.09.2011, 20:11 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3250
Репутация: 707 ±
Замечаний: 0% ±

2019
Вам нужно, чтобы это так выглядело, или сами данные преобразовать?
А то есть такой вариант - см.файл
К сообщению приложен файл: CustomFormat.xls (17.5 Kb)


excel@nxt.ru
webmoney: E265281470651 Z422237915069
 
Ответить
СообщениеВам нужно, чтобы это так выглядело, или сами данные преобразовать?
А то есть такой вариант - см.файл

Автор - Hugo
Дата добавления - 07.09.2011 в 20:11
RAN Дата: Среда, 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
 
Ответить
СообщениеДля ячейки в соседнем столбце справа
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:37
Формуляр Дата: Среда, 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
 
Ответить
СообщениеА можно одной формулой:
Code
= TEXT(A1,"000-000-000000")

Автор - Формуляр
Дата добавления - 07.09.2011 в 20:46
nilem Дата: Среда, 07.09.2011, 20:57 | Сообщение № 5
Группа: Авторы
Ранг: Старожил
Сообщений: 1613
Репутация: 563 ±
Замечаний: 0% ±

Excel 2013, 2016
Quote (RAN)
А если страшно...

если не очень страшно smile
Code
Dim s$
s = ActiveCell.Previous.Text
ActiveCell = "0" & Mid(s, 1, 2) & "-" & Mid(s, 3, 3) & "-" & Mid(s, 6)


Яндекс.Деньги 4100159601573
 
Ответить
Сообщение
Quote (RAN)
А если страшно...

если не очень страшно smile
Code
Dim s$
s = ActiveCell.Previous.Text
ActiveCell = "0" & Mid(s, 1, 2) & "-" & Mid(s, 3, 3) & "-" & Mid(s, 6)

Автор - nilem
Дата добавления - 07.09.2011 в 20:57
RAN Дата: Среда, 07.09.2011, 21:08 | Сообщение № 6
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

2010
Или
Code
Sub bb(): ActiveCell = Format(ActiveCell, "000-000-000000"): End Sub

biggrin


Быть или не быть, вот в чем загвоздка!
 
Ответить
СообщениеИли
Code
Sub bb(): ActiveCell = Format(ActiveCell, "000-000-000000"): End Sub

biggrin

Автор - RAN
Дата добавления - 07.09.2011 в 21:08
_Boroda_ Дата: Среда, 07.09.2011, 21:09 | Сообщение № 7
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

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
 
Ответить
СообщениеЕсли формулой, то можно так:
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_
Дата добавления - 07.09.2011 в 21:09
Ed_Vard Дата: Среда, 07.09.2011, 23:38 | Сообщение № 8
Группа: Пользователи
Ранг: Участник
Сообщений: 82
Репутация: 0 ±
Замечаний: 0% ±

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


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

Автор - Ed_Vard
Дата добавления - 07.09.2011 в 23:38
Hugo Дата: Среда, 07.09.2011, 23:54 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3250
Репутация: 707 ±
Замечаний: 0% ±

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

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


excel@nxt.ru
webmoney: E265281470651 Z422237915069
 
Ответить
СообщениеЕсли Variable not defined - значит напишите выше
Dim c as long

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

Автор - Hugo
Дата добавления - 07.09.2011 в 23:54
RAN Дата: Четверг, 08.09.2011, 00:06 | Сообщение № 10
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

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


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

Сообщение отредактировал RAN - Четверг, 08.09.2011, 00:26
 
Ответить
СообщениеВыделяем нужное и давим, давим... smile

Автор - RAN
Дата добавления - 08.09.2011 в 00:06
Ed_Vard Дата: Четверг, 08.09.2011, 00:12 | Сообщение № 11
Группа: Пользователи
Ранг: Участник
Сообщений: 82
Репутация: 0 ±
Замечаний: 0% ±

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

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


Сообщение отредактировал Ed_Vard - Четверг, 08.09.2011, 00:13
 
Ответить
Сообщение
Quote (RAN)
Выделяем нужное и давим, давим... smile
К сообщению приложен файл: 7150195.xls(36Kb)

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

Автор - Ed_Vard
Дата добавления - 08.09.2011 в 00:12
Ed_Vard Дата: Четверг, 08.09.2011, 00:20 | Сообщение № 12
Группа: Пользователи
Ранг: Участник
Сообщений: 82
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - Ed_Vard
Дата добавления - 08.09.2011 в 00:20
RAN Дата: Четверг, 08.09.2011, 00:27 | Сообщение № 13
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

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

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


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

Сообщение отредактировал RAN - Четверг, 08.09.2011, 00:40
 
Ответить
СообщениеХотел без цикла. Не получилось.
Перезалил файл.

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

Автор - RAN
Дата добавления - 08.09.2011 в 00:27
Ed_Vard Дата: Вторник, 04.10.2011, 19:46 | Сообщение № 14
Группа: Пользователи
Ранг: Участник
Сообщений: 82
Репутация: 0 ±
Замечаний: 0% ±

Доброго времени суток Всем форумчанам 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
 
Ответить
СообщениеДоброго времени суток Всем форумчанам 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

Автор - Ed_Vard
Дата добавления - 04.10.2011 в 19:46
RAN Дата: Вторник, 04.10.2011, 21:20 | Сообщение № 15
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

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


Быть или не быть, вот в чем загвоздка!
 
Ответить
Сообщение
Code
ActiveCell = Left(ActiveCell, 11) & " " & Right(ActiveCell, 2)

Автор - RAN
Дата добавления - 04.10.2011 в 21:20
nilem Дата: Вторник, 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
 
Ответить
СообщениеВариант для примера:
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

Автор - nilem
Дата добавления - 04.10.2011 в 22:07
Ed_Vard Дата: Среда, 26.10.2011, 15:39 | Сообщение № 17
Группа: Пользователи
Ранг: Участник
Сообщений: 82
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - Ed_Vard
Дата добавления - 26.10.2011 в 15:39
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Преобразование данных
  • Страница 1 из 1
  • 1
Поиск:

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