Hi

I have an export from a database which is giving me mobile phone numbers in a column. When input, some of these phone numbers were input with spaces, whereas some were not. We need to be uniform and remove all spaces.

Current = 027 123 4567
required = 0271234567

1) Can this be easily done without manually editing each cell?

2) The second phase of this is to remove all of the prefixed '0'

required = 271234567

3) The last phase is to add +64 as a prefix

required = +64271234567

In relation to 3) above I assume I use merge cell facility but am stuck on 1) and 2)

help would be appreciated



thank you

A simple Find/Replace is sufficient to delete all the spaces and leading 0s, where:
Find = <space>
Replace = <nothing>

To add the +64 prefix you could either:
1. input 64000000000 into a cell, copy it to the clipboard, select the cells to update and use Paste Special>Values>Add, plus a custom number format (+0) to add the + prefix; or
2. input '+64 into the cell adjacent to the existing number and, in the cell next to that, use a formula like =B1&A1 to concatenate the two (where the original number is in A1 and the '+64 is in B1), then copy the output and use Paste Special>Values to delete the formula.
The first method retains the numbers as numeric data; the second converts them to text.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

perfect thank you. Of interest though, what it the logic that that got rid of the leading zeros?

I'll try to work the next piece out this evening

your help is appreciated

By default, Excel doesn't display leading 0s in numeric data. The F/R converted your data format.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

sorry - struggling with this one, any further detail that would help me please?

Edit - sorted it out - used flash fill after manually inputting the first one. Text is fine for the use I have. Thank you