I have the column as you can see in this image.
The Distributor Address is in the format: "Street Address, Postcode, State".
I need to retrieve only "Postcode" and "State" and combine them.
The new column should be like this ➡"NSW2007","VIC3182"...
How could I retrieve the specific letters and combine them?
You can use FILTERXML if you have the newest version of Excel. See Excel - Extract substring(s) from string using FILTERXML for an excellent overview.
In this case, something like the below should work:
EDIT: overlooked the specific format you wanted
=FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s[3]")&
FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s[2]")
Well, here is a different way:
=RIGHT(A2,3)&","&TRIM(MID(A2,FIND(",",A2,1)+2,LEN(A2)-FIND(",",A2,FIND(",",A2,1)+1)))
This does rely on the state being the last 3 characters and the postcode after the first comma...
There are several ways of solving that. I will show a solution using a few basic functions: RIGHT, MID and LEN.
Assuming your data is on A1:
=RIGHT(A1,3)&MID(A1, LEN(A1)-8, 4)
RIGHT returns the 3 last characters from the cell A1.
MID returns the middle of the cell given a starting position and a length.
LEN gives you the starting position of the zip code, which is always the length of the string - 8 in your table.
Is it essential that your data is well organized as in the image to work well. One alternative is finding the ", " as below.
=RIGHT(A1,3)&MID(A1, FIND(", ",A1)+2,4)
