CODEWITHSUNDEEP
excel
Spionred
Spionred

Reputation: 807

Trimming cell with multiple carriage returns

I have a spreasheet from an csv formatted exported report which contains an address field with multiple lines.

Here is an example of the cell contents (I have used an image to show the question mark at the end of each line):

enter image description here

I need to extract the suburb (in this case Brooklyn) from the address however the challeneg I have is there are some cases where there is an extra line in the address, for example:

enter image description here

In all cases the Suburb will always be the second to last line so my though was to somehow work backwards but I have no idea how to do it as a formula.

ANy help would be very much appreciated.

Upvotes: 0

Views: 109

Answers (1)

Joseph B
Joseph B

Reputation: 5669

Let us say that the address is located in cell A1. Then, the following value would give you the last but one line.

It is derived by using the following pseudo-code:

MID
(A1, 
(the position of the [n-1]th new-line character) + 1, 
(the position of the [n]th new-line characters) - (the position of the n-1 new-line characters) - 1)

which is implemented using the formula below:

The # of occurrences of the new-line character is given by:

LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))

The [n]th occurrence of the new-line character is given by:

FIND(CHAR(1),SUBSTITUTE(A1,"
",CHAR(1),(*n*-1)))

Thus, the final formula is given by:

">"&MID(A1,FIND("
",A1,FIND("
",A1)+((LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))-2))+1,(FIND(CHAR(1),SUBSTITUTE(A1,"
",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))-(FIND("
",A1,FIND("
",A1)+((LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))-2)))-1)&"<"

As you can see, > and < characters are concatenated in the final formula to ensure that there are no extra characters in the final string. You may omit these concatenations.

Reference:

How to find nth occurrence (position) of a character in text string in Excel? on ExtendOffice

Upvotes: 2

Related Questions