• Status: Solved
• Priority: Medium
• Security: Public
• Views: 232

# how to Update a field in excel

I have a whole list of mobile phone numbers that were given with spaces etc in them and some that are not.  i have removed the spaces. but need to put a 0 in front of the number

ie 402130163

needs to be 0402130163

Cannot think today have the flue can someone plesae asist.
0
Amanda Walshaw
4 Solutions

Commented:
First thing that pops into my mind as a quick and dirty is to insert a column in front of the phone number, fill with 0, and then merge the two columns.
0

Commented:
Hi Flyfishtrout15,

you can change the field format to 0000000000 or add another column with the formula =text(a1,"0000000000")

0

Commented:
There's a couple of ways to do this, but here's one:

1. Go to a free column
2. Let's say that the first phone number exists B1.  So in the new column, type in:
=("0"&B1)

This will combine "0" with whatever is in B1.

3. Now, extend this formula down to the end of the the phone number list.

Here are some references:
http://office.microsoft.com/en-us/excel/HP052510211033.aspx#MergeCellContents
http://office.microsoft.com/en-us/excel/HP052090201033.aspx
0

Commented:
This code will quickly update your current numbers insitu - so you won't have to copy and paste other cells back over your originals manually
to use
'Press Alt + F11 to open the Visual Basic Editor (VBE)
'Paste the code into the right-hand code window.
'Press Alt + F11 to close the VBE
'Go to Tools & Macro & Macros and double-click Clearer

Cheers
Dave

``````Sub Clearer()
Dim rng1 As Range, rngArea As Range, i As Long, j As Long
Dim X()
Dim AppCalc As Long

'code runs on the active selection
Set rng1 = Selection

With Application
AppCalc = .Calculation
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

For Each rngArea In rng1.Areas
rngArea.NumberFormat = "@"
If rngArea.Cells.Count > 1 Then
X = rngArea
For i = 1 To rngArea.Rows.Count
For j = 1 To rngArea.Columns.Count
X(i, j) = "0" & X(i, j)
Next j
Next i
rngArea = X
Else
rngArea.Value = "0" & rngArea
End If
Next rngArea

With Application
.ScreenUpdating = True
.Calculation = AppCalc
End With
End Sub
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.