Thread: [Solved] removing spaces in cell
View Single Post
 
Old 11-06-2016, 05:46 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,467
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default
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]
Reply With Quote