Solved

Move postcode into a separate column

Posted on 2014-10-30
5
164 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 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
Comment Utility
Hi,

pls try

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

Regards
Postcode-separationV1.xlsx
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
=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
Comment Utility
Thanks heaps!
0
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
Corrected

=RIGHT(B3,4)

no space at the beginning that way
0
 

Author Comment

by:gregfthompson
Comment Utility
I fixed that. Thanks anyway!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now