How to get last item in white space separated words in cell?

Question

For example I have 500+ rows of data that is formatted as example: Hamilton Washington 30 OR 12345

The issue is the last item in the cell is a zip code and I need to copy it into another cell. The number of items in each cell is not the same so split text leaves it in different columns.

I essentially want the "12345" or last item in the cell since for all of them it will be the zip code.

Thanks

Answer 1

Suppose that your original string data is in A2:A. You could place the following formula in B2 (assuming B2:B is empty first):

=ArrayFormula(IF(A2:A="",,REGEXEXTRACT(A2:A,"\s([\S]+)$")))

This means "If a cell in A2:A is blank, leave the corresponding cell in B2:B blank; otherwise, return the last group of non-spaces that follows a space and ends with the end of the string."

Answer 2

You can try this function below & adjust it to your own setup:

=ARRAYFORMULA(TRIM(RIGHT(SUBSTITUTE(A1:A," ",REPT(" ",10)),10)))

Sample Result:

Reference:

Extract the Last Word In Excel & Google Sheets

TRIM

RIGHT

SUBTITUTE

REPT