Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Address text extraction question


how the way to get NSW2007 from this text?

310 Wattle StUltimo, 2007, NSW

Post Edited
Title: Title was not descriptive.


Next time please put the relevant information into the body of your question and have the title describe a general description of what you'd like to do. I updated it for you this time.
don (rep: 1979) Nov 19, '20 at 10:44 am
Add to Discussion




You can use the string functions RIGHT, LEN (=length of text in a cell) and MID to do that...

If cell A1 contains that address, put this in another cell:

=RIGHT(A1,3) & MID(A1,LEN(A1)-8,4)

(change the bits in bold if the address is some other cell).

The RIGHT bit gets the last three characters of the cell contents, the & combines that string with the output of the MID function. That picks text from mid-way in the cell, beginning at its length - note the formula =len(A1) would will give the result 30 (characters) - LESS 8 (so the 22nd character) then returns the next 4 characters i.e. the 2007.

Hope this helps.



I like how this question came right after I told you not to worry too much about titles haha.
don (rep: 1979) Nov 19, '20 at 10:45 am
True but I did follow your guidance, Don! Haha!
John_Ru (rep: 3992) Nov 19, '20 at 10:53 am
I appreciate that! This one was bad enough even for me to change haha.
don (rep: 1979) Nov 19, '20 at 10:55 am
Add to Discussion

If the commas are always in the same place and you don't want to use a more complex formula, you can use the Text-to-Columns feature and use a comma as a delimiter and then, once you get multiple columns of data, just make a new column to the right that is something like =C1&B1 and copy that down for the entire list.

Then, you can select that new column and hit Ctrl + C and then Alt + E + S + V to copy/paste special values and all of those formulas will be changed into the text that they output.


Answer the Question

You must create an account to use the forum. Create an Account or Login