Find and replace specific line and turn into UPPERcase

[DM115]

We have a load of addresses in a database. They are individually pasted into a Long Text field.

The issue:

Some addresses are in this format - 

51A Wigton Road

Carlisle

CA2 7AY

And some addresses are in this format- 

51A Wigton Road

CARLISLE

CA2 7AY

So the difference is that the Post Town is uppercase - which is what we want.

But a lot of submissions have come through in lower case on that second to last line, which is incorrect.

I need a formula to find and replace the all text before the last break and after the second to last break, ie. all text on the second to last line. And replace it with the same text but in UPPERcase. 

The addresses are not always on 4 lines, sometimes they are on 5 lines, so we would need to work backwords from the last break.

A formula to do this would be much appreciated. 

Thank you in advance.

[TheTimeSavingCo · Accepted solution]

Does this look right?

 

SUBSTITUTE(
  Address,
  REGEX_EXTRACT(Address, "(?:.*\n)?(.*)\n.*$"),
  UPPER(REGEX_EXTRACT(Address, "(?:.*\n)?(.*)\n.*$"))
)

[DM115]

Great stuff - thank you!