|
How do I put spaces in a field
Office 2003 & 2007, Win XP Pro
I have a list of telephone numbers eg: 0117839178 0866610689 0917142591133 I wish to convert them to: 011 783 9178 086 661 0678 091 714 259 1133 All numbers begin with zero. Much appreciated. |
How do I put spaces in a field
Perhaps something like this might suffice
In B1, copied down: =IF(LEN(A1)=10,LEFT(A1,3)&" "&MID(A1,4,3)&" "&RIGHT(A1,4),IF(LEN(A1)=13,LEFT(A1,3)&" "&MID(A1,4,3)&" "&MID(A1,7,3)&" "&RIGHT(A1,4),"")) -- Max Singapore http://savefile.com/projects/236895 Downloads: 15,500, Files: 352, Subscribers: 53 xdemechanik --- "Stan in South Africa" <me@there wrote in message ... Office 2003 & 2007, Win XP Pro I have a list of telephone numbers eg: 0117839178 0866610689 0917142591133 I wish to convert them to: 011 783 9178 086 661 0678 091 714 259 1133 All numbers begin with zero. Much appreciated. |
How do I put spaces in a field
That may need changing if the inputs are not text but numbers formatted with
a leading zero. -- David Biddulph "Max" wrote in message ... Perhaps something like this might suffice In B1, copied down: =IF(LEN(A1)=10,LEFT(A1,3)&" "&MID(A1,4,3)&" "&RIGHT(A1,4),IF(LEN(A1)=13,LEFT(A1,3)&" "&MID(A1,4,3)&" "&MID(A1,7,3)&" "&RIGHT(A1,4),"")) -- Max Singapore http://savefile.com/projects/236895 Downloads: 15,500, Files: 352, Subscribers: 53 xdemechanik --- "Stan in South Africa" <me@there wrote in message ... Office 2003 & 2007, Win XP Pro I have a list of telephone numbers eg: 0117839178 0866610689 0917142591133 I wish to convert them to: 011 783 9178 086 661 0678 091 714 259 1133 All numbers begin with zero. Much appreciated. |
| All times are GMT +1. The time now is 08:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com