Solved

# Move postcode into a separate column

Posted on 2014-10-30
178 Views
The attached example file contains a street address, suburb name and postcode in the same column.
I am seeking to have the postcode moved to it's own column.
Postcode-separation-issue-for-EE.xlsx
0
Question by:gregfthompson
• 2
• 2

LVL 49

Accepted Solution

Rgonzo1971 earned 500 total points
ID: 40412956
Hi,

pls try

=LEFT(B3,LEN(B3)-5) and =RIGHT(B3,5)

Regards
Postcode-separationV1.xlsx
0

LVL 25

Expert Comment

ID: 40412961
=UPPER(TRIM(RIGHT(SUBSTITUTE(TRIM(B3)," ",REPT(" ",99)),99)))

and then =LEFT(B3,LEN(B3)-LEN(UPPER(TRIM(RIGHT(SUBSTITUTE(TRIM(B3)," ",REPT(" ",99)),99)))))
Postcode-separation-issue-for-EE.xlsx
0

Author Closing Comment

ID: 40412991
Thanks heaps!
0

LVL 49

Expert Comment

ID: 40412993
Corrected

=RIGHT(B3,4)

no space at the beginning that way
0

Author Comment

ID: 40413154
I fixed that. Thanks anyway!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦