A macro to Move cursor inside of a cell

[lamore48]

Hi All,
I have what is probably an easy one for you pros out there but not so easy for me(novice). Anyway, I have a column of numbers about a 100 rows deep and the format of the numbers is always the same. An example would be:

102834394247
212532343642
31216192736

What these numbers are is a series of 2 digit numbers, so they should look like this:

10-28-34-39-42-47
21-25-32-34-36-42
3-12-16-19-27-36

So I thought I could record a macro to go to the cell, move the cursor to the right ending character, move left 2 characters, put a dash, move left 3, put a dash, move left 3, put a dash, move left 3, put a dash, move left 3, put a dash, and then move down one cell.

My macro ended up looking like this when I created it on the first series of numbers:

Sub Macro1()
'
'Macro1 Macro
'
Keyboard Shortcut: Ctrl+z
'
ActiveCell.FormulaR1C1 ="10-28-34-39-42-47"
ActiveCell.Offset(-2, -5).Range("A1").Select
End Sub

I even clicked on the Use Relative References
but every time I select a cell and run that Macro, I get the same 6 numbers. How do I write a macro that when I run it, it moves the cursor to the far right, counts 2 spaces left adds a dash, counts 3 spaces left adds a dash and so on?

Thanks in Advance,
Lenny

 

[Jonmo1]

Once the cursor is put into the cell, Excel is in Edit Mode.
Macros cannot run (or record) while in Edit Mode.
So the recorder is really only seeing the final result after you press enter, not the actions that took place to create that final result.

Is it always 6 sets of 2 digit numbers (the last number appears to begin with a single digit 3) ?

 

[lamore48]

Once the cursor is put into the cell, Excel is in Edit Mode.
Macros cannot run (or record) while in Edit Mode.
So the recorder is really only seeing the final result after you press enter, not the actions that took place to create that final result.

Is it always 6 sets of 2 digit numbers (the last number appears to begin with a single digit 3) ?

Well it would of been 03

 

[Jonmo1]

Well it would of been 03

What do you mean?
do you mean the cell would actually contain
031216192736

Or the cell actually contains
31216192736
and you want the result to be
03-12-16-19-27-36

It's an important difference that we need to know (this can be done, but we need to know the specific pattern of source data)

 

[Jonmo1]

Here's a formula method you could probably use
And you could then use the macro recorder to record yourself entering the formula..

=TEXT(TEXT(A1,"000000000000"),"00-00-00-00-00-00")

 

[lamore48]

What do you mean?
do you mean the cell would actually contain
031216192736

Or the cell actually contains
31216192736
and you want the result to be
03-12-16-19-27-36

It's an important difference that we need to know (this can be done, but we need to know the specific pattern of source data)

The cell actually contains
31216192736
and want the result to be
03-12-16-19-27-36

 

[Jonmo1]

Try the formula I posted.

I know you wanted VBA, so you can use the macro recorder to record yourself entering that formula.