Add a 0 to the start of a cell if the first number is a 7

[tommo_5]

Hello

I am running an extract to excel from a HR system at work which provides a mobile number for each employee. I need to add a 0 to all of the mobile numbers which begin with a 7 before i export the data to another system, is there an easy way to do this as i have over 1500 entries which need updating.

Thanks

JT

 

[wigi]

Hi

use a helper column with a formula like:

=IF(LEFT(A1,1)="7","0","")&A1

I assume phone numbers are in A1:A...

Drag down the formula far enough. Then copy the cells and paste as values over column A cells. Clean up the helper column and you're done.

Wigi

 

[kpark91]

=IF(LEFT(A1,1) = "7",CONCATENATE("0", A1), A1)

in helper column

 

[kpark91]

VBA Solution

For i = 1 To Range("A" & rows.count).End(xlUp).Row
    If Left(Range("A" & i).Value, 1) = "7" Then
        Range("A" & i).Value = "0" & Range("A" & i).Value
    End If
Next i

Does not need helper columns

 

[tommo_5]

Works a treat!

Thanks Very Much!

JT