Excel IF statement for trim function

Question

Good afternoon,

I would like to have a fixed trim address, when it's possible.

My address is:

** 15-21 Bournemouth rd, Aberdeen, AB11 6YA**

I want to get rid of the postcode and keep the address form like this:

15-21 Bournemouth rd, Aberdeen,

In this event I have to use the TRIM function from the left, what I did.

I tried these 2 formulas:

 =LEFT(TRIM(D18),FIND("^",SUBSTITUTE(TRIM(D18)&" "," ","^",4))-2)

and

 =TRIM(LEFT(D18, FIND("~",SUBSTITUTE(D18, " ", "~",5)&"~")))

which gives me the results:

15-21 Bournemouth rd, Aberdeen

15-21 Bournemouth rd, Aberdeen,

then is fine. I am happy.

Although sometimes my address comes with the own name of the premise, like this:

21-23 Regis House 15-21 Bournemouth rd, Aberdeen, AB11 6YA

and then the issue becomes more complicated, as I cannot apply these formulas (I have to change them manually, which I don't want).

In the result of the same formula parameters I am getting accordingly:

21-23 Regis House 15-2

23 Regis House, 15-21

which is not good, as I need a full address without the postcode only.

I cannot change these formulas manually. On top of that, when I swap them to the right side, then the rightmost stuff is being kept.

I found something here:

Combining trim and if formula

the IF statement for TRIM function, and net tried to plot it into my example:

   =IF(NOT(SUM(COUNTIF(A18, "*"&{",",","}&"*"))), TRIM(LEFT(SUBSTITUTE(MID(A18,FIND("|",SUBSTITUTE(A18,"-","|",2))+1,LEN(A18)),"-",REPT(" ",LEN(A18))),LEN(A18))), "")

which returns #VALUE.

How can I solve it?

Answer 1

IF we can assume your postcode always follows the last comma in your string you can use the below to extract everything before the last comma:

=MID(A1,1,FIND("@",SUBSTITUTE(A1,",","@",LEN(A1)-LEN(SUBSTITUTE(A1,",","")))))

Note this also assumes that @ will not be in your strings anywhere. We essentially want to replace your target comma with a symbol that will be unique to all other characters in any string

---

VBA

If you are open to VBA solution, you can use the below UDF to get the same results

Public Function LASTCOMMA(Target As String)

Dim i As Long, Arr
Arr = Split(Target, ",")

For i = LBound(Arr) To (UBound(Arr) - 1)
    LASTCOMMA = LASTCOMMA & Arr(i)
Next i

End Function