Link to home
Start Free TrialLog in
Avatar of gregfthompson
gregfthompsonFlag for Australia

asked on

Move postcode into a separate column

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
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Professor J
Professor J
=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
Avatar of gregfthompson
gregfthompson
Flag of Australia image

ASKER

Thanks heaps!
Avatar of Rgonzo1971
Rgonzo1971
Corrected

=RIGHT(B3,4)

no space at the beginning that way
Avatar of gregfthompson
gregfthompson
Flag of Australia image

ASKER

I fixed that. Thanks anyway!