| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format of Postcodes
I have a formula, but it does not quite meet with my needs.
=REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1))) gives the result: Eg: AB19PQ PE548YT Changes it to: AB1 9PQ PE54 8YT The 7-digit postcode is spaced correctly, but I would like an extra space to appear in the 6-digit postcode i.e. AB 1 9PQ Any ideas? Thanks. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Format of Postcodes
My solution for the 6 digit codes slightly different than your for the 7
digit ones but try this:- =IF(LEN(A1)=7,REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1))),LEFT(A1,2)&" "&MID(A1,3,1)&" "&MID(A1,4,3)) Mike "pseudonym" wrote: I have a formula, but it does not quite meet with my needs. =REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1))) gives the result: Eg: AB19PQ PE548YT Changes it to: AB1 9PQ PE54 8YT The 7-digit postcode is spaced correctly, but I would like an extra space to appear in the 6-digit postcode i.e. AB 1 9PQ Any ideas? Thanks. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Format of Postcodes
Why does this put a square symbol in the newly-created space?
On 18 May, 12:21, Mike H wrote: My solution for the 6 digit codes slightly different than your for the 7 digit ones but try this:- =IF(LEN(A1)=7,REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1))),LEFT(A1,2)&" "&MID(A1,3,1)&" "&MID(A1,4,3)) Mike "pseudonym" wrote: I have a formula, but it does not quite meet with my needs. =REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1))) gives the result: Eg: AB19PQ PE548YT Changes it to: AB1 9PQ PE54 8YT The 7-digit postcode is spaced correctly, but I would like an extra space to appear in the 6-digit postcode i.e. AB 1 9PQ Any ideas? Thanks. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Format of Postcodes
It doesn't do it on my machine and If it does on yours then I'm confused!!
All it does is concatenate the 6 digit postcode with spaces. Any chance of a paste of what it looks like. Mike "pseudonym" wrote: Why does this put a square symbol in the newly-created space? On 18 May, 12:21, Mike H wrote: My solution for the 6 digit codes slightly different than your for the 7 digit ones but try this:- =IF(LEN(A1)=7,REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1))),LEFT(A1,2)&" "&MID(A1,3,1)&" "&MID(A1,4,3)) Mike "pseudonym" wrote: I have a formula, but it does not quite meet with my needs. =REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1))) gives the result: Eg: AB19PQ PE548YT Changes it to: AB1 9PQ PE54 8YT The 7-digit postcode is spaced correctly, but I would like an extra space to appear in the 6-digit postcode i.e. AB 1 9PQ Any ideas? Thanks. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Format of Postcodes
Thanks people I thought I was having (another) elderly moment!!
"Mike H" wrote: It doesn't do it on my machine and If it does on yours then I'm confused!! All it does is concatenate the 6 digit postcode with spaces. Any chance of a paste of what it looks like. Mike "pseudonym" wrote: Why does this put a square symbol in the newly-created space? On 18 May, 12:21, Mike H wrote: My solution for the 6 digit codes slightly different than your for the 7 digit ones but try this:- =IF(LEN(A1)=7,REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1))),LEFT(A1,2)&" "&MID(A1,3,1)&" "&MID(A1,4,3)) Mike "pseudonym" wrote: I have a formula, but it does not quite meet with my needs. =REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1))) gives the result: Eg: AB19PQ PE548YT Changes it to: AB1 9PQ PE54 8YT The 7-digit postcode is spaced correctly, but I would like an extra space to appear in the 6-digit postcode i.e. AB 1 9PQ Any ideas? Thanks. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Format of Postcodes
Thanks everyone
On 18 May, 13:29, Mike H wrote: Thanks people I thought I was having (another) elderly moment!! "Mike H" wrote: It doesn't do it on my machine and If it does on yours then I'm confused!! All it does is concatenate the 6 digit postcode with spaces. Any chance of a paste of what it looks like. Mike "pseudonym" wrote: Why does this put a square symbol in the newly-created space? On 18 May, 12:21, Mike H wrote: My solution for the 6 digit codes slightly different than your for the 7 digit ones but try this:- =IF(LEN(A1)=7,REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1))),LEFT(A1,2)&" "&MID(A1,3,1)&" "&MID(A1,4,3)) Mike "pseudonym" wrote: I have a formula, but it does not quite meet with my needs. =REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1))) gives the result: Eg: AB19PQ PE548YT Changes it to: AB1 9PQ PE54 8YT The 7-digit postcode is spaced correctly, but I would like an extra space to appear in the 6-digit postcode i.e. AB 1 9PQ Any ideas? Thanks. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Format of Postcodes
You are probably suffering from line-wrap if you pasted the formula
from the posting. The middle part of the formula should be: .... LEFT(A1,2)&" "&MID(A1,3,1) ... but you probably have a different character between the quotes. Hope this helps. Pete On May 18, 12:27 pm, pseudonym wrote: Why does this put a square symbol in the newly-created space? On 18 May, 12:21, Mike H wrote: My solution for the 6 digit codes slightly different than your for the 7 digit ones but try this:- =IF(LEN(A1)=7,REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1))),LEFT(A1,2)&" "&MID(A1,3,1)&" "&MID(A1,4,3)) Mike "pseudonym" wrote: I have a formula, but it does not quite meet with my needs. =REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1))) gives the result: Eg: AB19PQ PE548YT Changes it to: AB1 9PQ PE54 8YT The 7-digit postcode is spaced correctly, but I would like an extra space to appear in the 6-digit postcode i.e. AB 1 9PQ Any ideas? Thanks.- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Format of Postcodes
An unfortunate line wrap:
=IF(LEN(A1)=7,REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1))), LEFT(A1,2)&" "&MID(A1,3,1)&" "&MID(A1,4,3)) (still one cell) pseudonym wrote: Why does this put a square symbol in the newly-created space? On 18 May, 12:21, Mike H wrote: My solution for the 6 digit codes slightly different than your for the 7 digit ones but try this:- =IF(LEN(A1)=7,REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1))),LEFT(A1,2)&" "&MID(A1,3,1)&" "&MID(A1,4,3)) Mike "pseudonym" wrote: I have a formula, but it does not quite meet with my needs. =REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1))) gives the result: Eg: AB19PQ PE548YT Changes it to: AB1 9PQ PE54 8YT The 7-digit postcode is spaced correctly, but I would like an extra space to appear in the 6-digit postcode i.e. AB 1 9PQ Any ideas? Thanks. -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Format of Postcodes
Hi
It has been created by the wrapping of the formula in the NG reader. This wrapping happens to take place in the middle of the space between the double quotes. After pasting the formula into the formula bar of your sheet, it will be on 2 lines. Place the cursor at the end of the first line and press Delete once. That will bring the formula onto one line and the symbol will disappear. Below I have reproduce Mike's formula, but forced the break to be after the comma. =IF(LEN(A1)=7,REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1))), LEFT(A1,2)&" "&MID(A1,3,1)&" "&MID(A1,4,3)) If that were copied and pasted, even though it is on multiple lines, you will not get the square symbol -- Regards Roger Govier "pseudonym" wrote in message ups.com... Why does this put a square symbol in the newly-created space? On 18 May, 12:21, Mike H wrote: My solution for the 6 digit codes slightly different than your for the 7 digit ones but try this:- =IF(LEN(A1)=7,REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1))),LEFT(A1,2)&" "&MID(A1,3,1)&" "&MID(A1,4,3)) Mike "pseudonym" wrote: I have a formula, but it does not quite meet with my needs. =REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1))) gives the result: Eg: AB19PQ PE548YT Changes it to: AB1 9PQ PE54 8YT The 7-digit postcode is spaced correctly, but I would like an extra space to appear in the 6-digit postcode i.e. AB 1 9PQ Any ideas? Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Format of postcodes | Excel Discussion (Misc queries) | |||
| Postcodes starting with 0 | Excel Discussion (Misc queries) | |||
| Standardising Postcodes | Excel Discussion (Misc queries) | |||
| Sorting UK Postcodes | Excel Worksheet Functions | |||
| Postcodes | Excel Worksheet Functions | |||
Hybrid Mode
