#1
|
|||
|
|||
removing spaces in cell
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 |
#2
|
||||
|
||||
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] |
#3
|
|||
|
|||
Quote:
I'll try to work the next piece out this evening your help is appreciated |
#4
|
||||
|
||||
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] |
#5
|
|||
|
|||
Quote:
Edit - sorted it out - used flash fill after manually inputting the first one. Text is fine for the use I have. Thank you |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
If value of cell A Matches a value in a Range of cells (column) then add value of cell A to cell C | rick10r | Excel | 1 | 07-05-2016 12:07 PM |
Removing characters from a cell | tonydoneese | Excel | 4 | 01-12-2016 07:41 AM |
How can I delete spaces & lines in a table cell | mrayncrental | Word VBA | 3 | 10-20-2014 07:09 PM |
Assign the value of a cell as a cell reference of another cell in Excel 2010 - How to? | bharathkumarst | Excel | 7 | 10-13-2014 10:25 AM |
Word-Help with removing unwanted spaces in text | greshoff | Word | 9 | 12-30-2011 03:24 PM |