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:
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?
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
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