LinkBack URL
About LinkBacks
Hi,
I am working with Addresses on this one.
format sample: 1 High Street Sydney NSW 2000
I would like to extract:
"1 High Street" in a different column
"Sydney NSW 2000" in a different column
Appreciate any help
Get text after 3rd space and text before 3rd space
Hi,
I am working with Addresses on this one.
format sample: 1 High Street Sydney NSW 2000
I would like to extract:
"1 High Street" in a different column
"Sydney NSW 2000" in a different column
Appreciate any help
Do you have more samples? What works with the one might not with others.
Edit.
If it's always the 3rd space as your title suggests try this. With the string in A1, this formula in B1 fill across.Formula:
Please Login or Register to view this content.
Last edited by FlameRetired; 12-04-2015 at 12:08 AM.
Dave
That works perfect in getting the street address (i.e "1 High Street") !!
How can i get the text string after the 3rd space? (i.e "Sydney NSW 2000")?
What I want is:
A1: 1 High Street Sydney NSW 2000
B1 = 1 High Street
C1 = Sydney NSW 2000
Yes. This formula does that. With the string in A1 enter that formula into B1. Grab the fill handle and fill across to C1.
Row\Col 1 High Street Sydney NSW 2000 1 High Street Sydney NSW 2000 In B1: =TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",99),3),(COLUMNS($A:A)-1)*99+1,99))
Last edited by FlameRetired; 12-04-2015 at 12:21 AM.
I didnt think to move the formula to the next column! it works like a charm!
You are welcome. Thanks for the feedback and rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks