CODEWITHSUNDEEP
excel
Tim Wilkinson
Tim Wilkinson

Reputation: 3791

Excel text to colum, reverse the outcome

I have a long list of addresses, the whole address is in a single cell, each 'line' of the address is separated by a comma. I need to be able to extract just the post code. An example of the current data is as follows,

Red House, Street, Town, City, YO13 9DY
Blue House, Street, YP8 2FR
Black House, Street, City, L10 1AA
Green House, Street, Street 2, Town, City, PO5T CDE

As you can see each address follows the same pattern, using the same comma as divider, however all are different lengths.

Text to columns works to some extent, but due to the layout of each cell, the post code always ends up in a different column.

Is there a way to reverse this output, so similar to Text to Columns, but each deliminator is printed in reverse order?

If I not explained this very well please say and I shall attempt to re-word.

Upvotes: 0

Views: 1076

Answers (2)

DaveG
DaveG

Reputation: 753

If the postal code is in a different column, you can take the value of the last (non-empty) column with the following code (put in H1 and other columns in B1-G1):

=IF(G1<>"";G1;IF(F1<>"";F1;IF(E1<>"";E1;IF(D1<>"";D1;IF(C1<>"";C1;IF(B1<>"";B1;""))))))

If your language is different, you might want to change all ; to ,.

You can easily expand if the number of columns is larger, or with other constraints,...

Upvotes: 0

Shperb
Shperb

Reputation: 474

Instead of using Text to columns you can use this to get only what is after the last comma :

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

(Assuming A1 contains the full address like in your example)

Upvotes: 2

Related Questions