Solved

Move postcode into a separate column

Posted on 2014-10-30
5
187 Views
Last Modified: 2014-10-30
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
Comment
Question by:gregfthompson
  • 2
  • 2
5 Comments
 
LVL 50

Accepted Solution

by:
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 26

Expert Comment

by:ProfessorJimJam
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

by:gregfthompson
ID: 40412991
Thanks heaps!
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40412993
Corrected

=RIGHT(B3,4)

no space at the beginning that way
0
 

Author Comment

by:gregfthompson
ID: 40413154
I fixed that. Thanks anyway!
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question