Hi,
I have a column with mobile telephone numbers. e.g. 0846578902. (The cell is
format to text not number)
Now I need to add a 27 in front of each to become 27846578902

I'm sure this can be done with a macro but am not too familiar with vb.
I had seen this in an earlier thread;

Sub Remove4()
Dim myCell As Range
For Each myCell In Selection
myCell.Value = Left(myCell.Value, Len(myCell.Value) - 4)
Next myCell
End Sub

But that deletes the last 4 characters. 'm not sure how it is identifying
the last 4!

You seem to be doing a little more than adding 27 in front of the phone
number... you also seem to be deleting the first character of the phone
number first. See if you can use one of these (depending on what you
actually want to do)...

To just add 27 in front of cell content...

Sub Add27()
Selection.Value = "27" & Selection.Value
End Sub

To replace the first character of cell with 27...

Sub Add27()
Selection.Value = "27" & Mid(Selection.Value, 2)
End Sub

Rick


"Graeme at Raptup" wrote in
message ...
Hi,
I have a column with mobile telephone numbers. e.g. 0846578902. (The cell
is
format to text not number)
Now I need to add a 27 in front of each to become 27846578902

I'm sure this can be done with a macro but am not too familiar with vb.
I had seen this in an earlier thread;

Sub Remove4()
Dim myCell As Range
For Each myCell In Selection
myCell.Value = Left(myCell.Value, Len(myCell.Value) - 4)
Next myCell
End Sub

But that deletes the last 4 characters. 'm not sure how it is identifying
the last 4!

I'm no VBA expert either, but I think you could change your example to:

Sub Add27()
Dim myCell As Range
For Each myCell In Selection
myCell.Value = "27" & Right(myCell.Value, Len(myCell.Value) - 1)
Next myCell
End Sub

You said: " But that deletes the last 4 characters. 'm not sure how it is
identifying the last 4!", to which the answer is that your Remove4 routine
uses the LEFT function, and the LEN function. [If you don't know how any
Excel functions work, look them up in Help.]

In the modified routine I have merely replaced the LEFT by RIGHT, and used
it to remove 1 character, not 4. I have concatenated the 27 on the front.
--
David Biddulph
"Graeme at Raptup" wrote in
message ...
Hi,
I have a column with mobile telephone numbers. e.g. 0846578902. (The cell
is
format to text not number)
Now I need to add a 27 in front of each to become 27846578902

I'm sure this can be done with a macro but am not too familiar with vb.
I had seen this in an earlier thread;

Sub Remove4()
Dim myCell As Range
For Each myCell In Selection
myCell.Value = Left(myCell.Value, Len(myCell.Value) - 4)
Next myCell
End Sub

But that deletes the last 4 characters. 'm not sure how it is identifying
the last 4!

Thanks Rick,
the macro works on one cell, but if I try and apply over more I get a type
mismatch error.

I also tried this but got the same error,
Sub Add27()
Dim myCell As Range
For Each myCell In Selection
myCell.Value = "27" & Selection.Value
Next myCell
End Sub

Cheers,

Graeme

"Rick Rothstein (MVP - VB)" wrote:

You seem to be doing a little more than adding 27 in front of the phone
number... you also seem to be deleting the first character of the phone
number first. See if you can use one of these (depending on what you
actually want to do)...

To just add 27 in front of cell content...

Sub Add27()
Selection.Value = "27" & Selection.Value
End Sub

To replace the first character of cell with 27...

Sub Add27()
Selection.Value = "27" & Mid(Selection.Value, 2)
End Sub

Rick


"Graeme at Raptup" wrote in
message ...
Hi,
I have a column with mobile telephone numbers. e.g. 0846578902. (The cell
is
format to text not number)
Now I need to add a 27 in front of each to become 27846578902

I'm sure this can be done with a macro but am not too familiar with vb.
I had seen this in an earlier thread;

Sub Remove4()
Dim myCell As Range
For Each myCell In Selection
myCell.Value = Left(myCell.Value, Len(myCell.Value) - 4)
Next myCell
End Sub

But that deletes the last 4 characters. 'm not sure how it is identifying
the last 4!

You can't use Selection within the For Each loop... you have to use cell
reference which the myCell variable takes on from the cells within the
selection, one at a time, during the loop

Sub Add27()
Dim myCell As Range
For Each myCell In Selection
myCell.Value = "27" & Mid(myCell.Value, 2)
Next
End Sub

Rick


"Graeme at Raptup" wrote in
message ...
Thanks Rick,
the macro works on one cell, but if I try and apply over more I get a type
mismatch error.

I also tried this but got the same error,
Sub Add27()
Dim myCell As Range
For Each myCell In Selection
myCell.Value = "27" & Selection.Value
Next myCell
End Sub

Cheers,

Graeme

"Rick Rothstein (MVP - VB)" wrote:

You seem to be doing a little more than adding 27 in front of the phone
number... you also seem to be deleting the first character of the phone
number first. See if you can use one of these (depending on what you
actually want to do)...

To just add 27 in front of cell content...

Sub Add27()
Selection.Value = "27" & Selection.Value
End Sub

To replace the first character of cell with 27...

Sub Add27()
Selection.Value = "27" & Mid(Selection.Value, 2)
End Sub

Rick


"Graeme at Raptup" wrote in
message ...
Hi,
I have a column with mobile telephone numbers. e.g. 0846578902. (The
cell
is
format to text not number)
Now I need to add a 27 in front of each to become 27846578902

I'm sure this can be done with a macro but am not too familiar with vb.
I had seen this in an earlier thread;

Sub Remove4()
Dim myCell As Range
For Each myCell In Selection
myCell.Value = Left(myCell.Value, Len(myCell.Value) - 4)
Next myCell
End Sub

But that deletes the last 4 characters. 'm not sure how it is
identifying
the last 4!

Thanks Rick,
I guess I was just lazy.
That makes sense.

Cheers,

Graeme

"Rick Rothstein (MVP - VB)" wrote:

You can't use Selection within the For Each loop... you have to use cell
reference which the myCell variable takes on from the cells within the
selection, one at a time, during the loop

Sub Add27()
Dim myCell As Range
For Each myCell In Selection
myCell.Value = "27" & Mid(myCell.Value, 2)
Next
End Sub

Rick


"Graeme at Raptup" wrote in
message ...
Thanks Rick,
the macro works on one cell, but if I try and apply over more I get a type
mismatch error.

I also tried this but got the same error,
Sub Add27()
Dim myCell As Range
For Each myCell In Selection
myCell.Value = "27" & Selection.Value
Next myCell
End Sub

Cheers,

Graeme

"Rick Rothstein (MVP - VB)" wrote:

You seem to be doing a little more than adding 27 in front of the phone
number... you also seem to be deleting the first character of the phone
number first. See if you can use one of these (depending on what you
actually want to do)...

To just add 27 in front of cell content...

Sub Add27()
Selection.Value = "27" & Selection.Value
End Sub

To replace the first character of cell with 27...

Sub Add27()
Selection.Value = "27" & Mid(Selection.Value, 2)
End Sub

Rick


"Graeme at Raptup" wrote in
message ...
Hi,
I have a column with mobile telephone numbers. e.g. 0846578902. (The
cell
is
format to text not number)
Now I need to add a 27 in front of each to become 27846578902

I'm sure this can be done with a macro but am not too familiar with vb.
I had seen this in an earlier thread;

Sub Remove4()
Dim myCell As Range
For Each myCell In Selection
myCell.Value = Left(myCell.Value, Len(myCell.Value) - 4)
Next myCell
End Sub

But that deletes the last 4 characters. 'm not sure how it is
identifying
the last 4!