Excel宏来拆分条目 [英] Excel macro to split an entry

问题描述

你好。

我试过搜索数据库，但我还没有找到答案，我确信这是一个简单的"真实"问题。 excel宏用户。

I've tried searching the database but I haven't found an answer to what I'm sure is a simple problem for "real" excel macro users.

我在一个字段中填写了很长的名单和电话号码列表，我想把这个号码拿出来放在第二个字段中。 。

I have long lists of names and telephone numbers that have been filled in in a single field and I want to take the number out and put it in a second field...

它看起来像这样：

我使用"相对引用"记录我的宏只需选择电话号码并将其剪切并粘贴到下一栏。

I record my macro using "relative references" just by electing the telephone number and cutting and pasting it to the next column.

宏（或至少其中一个我'已经尝试过）看起来像这样：

The macro (or at least one of the many I've tried) looks like this:

Sub Macro1（）

'

'Proose1 Macro

'¥
'Couche de raccourci du clavier：Ctrl + w

'

    ActiveCell.Select

    ActiveCell.FormulaR1C1 ="AXXXY  Gxxxxs" b $ b    ActiveCell.Offset（0,1）.Range（" A1"）。选择

    ActiveSheet.Paste

End Sub

Sub Macro1()
'
' Macro1 Macro
'
' Touche de raccourci du clavier: Ctrl+w
'
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "AXXXY  Gxxxxs "
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveSheet.Paste
End Sub

所以当我转到下一个条目时发生的一切是我在新行中得到相同的数字和相同的名字...我有时设法建立宏不改变名称，然后它只提出第一个参考号码...

So all that happens when I go to the next entry is that I get the same number and the same name in the new line... I've sometimes managed to build the macro not to change the name and then it only puts up the first referenc number...

我确定我在做某事错误的"相对参考"水平，但我无法理解... ...

I'm sure I'm doing something wrong at the "relative references" level but I haven't been able to fathom out what...

任何线索都会被感激地接受

Any clues would be gratefully accepted

Keith Braithwaite

Keith Braithwaite

推荐答案

Keith

Keith

您所提问题的解决方案如下。  但是，它确实需要电话号码的长度相同。 
因此，它不适用于包含国际代码的电话号码，也不适用于非法语格式的电话号码。 
所有代码都采用所选单元格中的最后14个字母/数字，并将它们移动到右边的下一个单元格。

A solution to what you have asked is below.  However, it does require that the phone numbers are all of the same length.  So it would not work for phone numbers that include the international code, or for phone numbers not in the French format.  All the code does is take the last 14 letters / numbers in the selected cell and move them to the next cell to the right.

Sub ShiftNumbers（）

Sub ShiftNumbers()

Dim strDetails As String

Dim strDetails As String

Dim strPhone As String

Dim strPhone As String

    strDetails = Selection.Value'整个单元格的值

    strDetails = Selection.Value 'Value of the whole cell

    strPhone =右（strDetails，14）'最右边14个字符的值

    strPhone = Right(strDetails, 14) 'value of the rightmost 14 characters

    Selection.Offset（0,1）.Value = strPhone'将电话号码放入右侧的单元格中

    Selection.Offset(0, 1).Value = strPhone 'put the phone number into the cell one to the right

    strDetails = Left（strDetails，Len（strDetails） - 15）'将所选单元格设置为除最后15个字符以外的所有字符

    strDetails = Left(strDetails, Len(strDetails) - 15) 'Set the selected cell to be all but the last 15 characters

    Selection.Value = strDetails'将新的截断值重新放回选定的单元格。   

    Selection.Value = strDetails 'Put the new, truncated value back into the selected cell.   

结束次郎

End Sub

录制宏的问题在于它确实存在不记录"剪切"命令。 
因此，您在录制时复制的项目仍保留在剪贴板上，以便后续运行宏。

The problem with recording your macro is that it did not record the "Cut" command.  So the item that you copied while recording remains on the clipboard for subsequent runs of the macro.

希望这有助于

AndyC

这篇关于Excel宏来拆分条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！