how the way to get NSW2007 from this text?
310 Wattle StUltimo, 2007, NSW
how the way to get NSW2007 from this text?
310 Wattle StUltimo, 2007, NSW
Culuulu
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.
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.