copy information

[cathal]

Each cell contains a full address. The country is on the last line.
How can I copy the country into an adjacent cell without going down th
"text to column" route?

thanks, cathal..

 

[Bernie Deitrick]

cathal,

If the country is the very last word in the cell, then you can use the array
formula (entered with Ctrl-Shift-Enter)

=RIGHT(A1,LEN(A1)-MAX((MID(A1,ROW(INDIRECT("1:"
& LEN(A1))),1)=" ")*ROW(INDIRECT("1:" & LEN(A1)))))

Put that all on one row. I broke it to prevent line wrapping on the " ".....

HTH,
Bernie
MS Excel MVP