Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.




21414 posts

Uber Geek

Trusted
Lifetime subscriber

# 217892 17-Jul-2017 14:47
Send private message

I have been sent a list of contacts, and the address is a 3 row cell (Column E) called address of which some addresses have 1 or 2 or 3 fields. If it's 3, the third is almost always the suburb, and if it's 1 it's never. The Majority of times it's 2 rows, it's Suburb in the second row.

 

I am trying to extract the suburb from the address field and insert it into the next column called suburb.

 

I do have post codes for almost every address, so I guess we could extract Suburb that way, but I don't know how to cross reference and populate it. 

 

I expect this isn't simple and probably isn't worth worrying about fixing with automation, but in case it is moderately straight forward, I'd be keen to hear about it. 

 

 


Create new topic
3393 posts

Uber Geek


  # 1823871 17-Jul-2017 15:37
Send private message

Example of a few fields?

 

 

 

You could do a data import and specify CR as the field separator. It would then split the multi cells into their own. 

 

 

 

 

I found out the other day that the carriage return can be used as a delimiter when converting text to columns. This is useful if you have in-cell carriage returns that you want to get rid of. (To do an in-cell carriage return in the first place, you just hit Alt+Enter.)

 

Highlight your column of data, making sure the columns to its right are empty. On the Data ribbon, hit Text to Columns. Hit the Delimited option, and then check the Other option. Click in the box where you are to type the delimiter and press CTRL+J.

 

Ala this may be what you are looking for?

 

http://datapigtechnologies.com/blog/index.php/split-data-into-several-columns-based-on-carriage-returns/ 


234 posts

Master Geek

Lifetime subscriber

  # 1824002 17-Jul-2017 19:04
Send private message

Off the top of my head I'm sure I have done a "select characters from. Right until first space".
Probably not much help cause I'm not telling you how, but I would check that out :-)

 
 
 
 


BDFL - Memuneh
64775 posts

Uber Geek

Administrator
Trusted
Geekzone
Lifetime subscriber

  # 1824038 17-Jul-2017 20:13
2 people support this post
Send private message

Pasting this on behalf of @Hammerer... It seems Cloudflare SQL Injection filters are too sensitive:

 

You can't use Ctrl+J in a formula. Instead Find the newline character ASCII 010 which is CHAR(10).

 

You can use a text function like SEARCH to find the character and then extract the text between the first character of the address and that newline. Then search for the next occurrence and extract the text between the previous newline and this newline. Keep doing that until you hit the end of the text.

 

In practice it is easier to break longer formulae into smaller because you can insert columns to hold the intermediate strings. So you would have columns with first line, remainder after first line, second line, remainder after second line, third line, remainder after third line,etc. Then you can use the same two formulae on the remaining string.

 

 

 

Here's some examples of relevant functions. There might be shorter formulae you can use - there often is - but I've stuck to the functions I know:

 

 

 

SEARCH finds a character in a string from a chosen start position. If no start position is provided then 1 is assumed.

 

=SEARCH(CHAR(10),A1,1) finds the position of the first newline from character 1.

 

=SEARCH(CHAR(10),A1,SEARCH(CHAR(10),A1)+1) finds the second newline after the first newline

 

 

 

MID extracts text from a string by start position and number of characters

 

=MID(A1,1,SEARCH(CHAR(10),A1,1)) extracts all characters before the newline

 

=MID(A1,(SEARCH(CHAR(10),A1,1)+1),LEN(A1)-(SEARCH(CHAR(10),A1,1)-1)) extracts all characters after the newline. Use this as the string (bold below) to search for the next newline.

 

=MID(MID(A1,(SEARCH(CHAR(10),A1,1)+1),LEN(A1)-(SEARCH(CHAR(10),A1,1)-1)),1,SEARCH(CHAR(10),A1,1)) extracts the second line.

 

 

 

I could have used LEFT instead of MID for the first line

 

=LEFT(A1,SEARCH(CHAR(10),A1,1))

 

 

 

I could have used RIGHT instead of MID to extract everything to the right of the newline

 

=RIGHT(A1,LEN(A1)-SEARCH(CHAR(10),A1,1)) extracts all characters to the right of the newline





2034 posts

Uber Geek

Lifetime subscriber

  # 1824039 17-Jul-2017 20:17
One person supports this post
Send private message

I should have added that all formulae rely on cell A1 containing the string.




21414 posts

Uber Geek

Trusted
Lifetime subscriber

  # 1824310 18-Jul-2017 12:19
Send private message

Thanks, it's all a little beyond me right now it seems, so I think I'll just not worry about it. Thanks very much for those who posted. 


3393 posts

Uber Geek


  # 1824318 18-Jul-2017 12:29
Send private message

networkn:

 

Thanks, it's all a little beyond me right now it seems, so I think I'll just not worry about it. Thanks very much for those who posted. 

 

 

If its any help. I made a cell with what I think you are describing

 

This
Is
Test

 

And using the text in my quote made it

 

This | Is | Test

 

in their own cells without issue. You could then manipulate that easier.


2034 posts

Uber Geek

Lifetime subscriber

  # 1824976 19-Jul-2017 09:16
Send private message

networkn:

 

Thanks, it's all a little beyond me right now it seems, so I think I'll just not worry about it. Thanks very much for those who posted. 

 

 

 

 

If you provide an example of the data then I could create an Excel conversion example. To ensure that it works as intended, it helps to have real data because, as they say, the devil is in the detail. For example, there can be different control character used in the original text.


 
 
 
 




21414 posts

Uber Geek

Trusted
Lifetime subscriber

  # 1825282 19-Jul-2017 14:49
Send private message

Hammerer:

 

networkn:

 

Thanks, it's all a little beyond me right now it seems, so I think I'll just not worry about it. Thanks very much for those who posted. 

 

 

 

 

If you provide an example of the data then I could create an Excel conversion example. To ensure that it works as intended, it helps to have real data because, as they say, the devil is in the detail. For example, there can be different control character used in the original text.

 

 

 

 

Thanks for your very kind offer. At this stage, I have manually done it but it's something I need to do a couple of times a year and it's pretty tedious. I'll try and find some sanitised data and send it through. Thank you again for the offer. 


Create new topic



Twitter and LinkedIn »



Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:





News »

Spark launches new wireless broadband "Unplan Metro"
Posted 11-Nov-2019 08:19


Malwarebytes overhauls flagship product with new UI, faster engine and lighter footprint
Posted 6-Nov-2019 11:48


CarbonClick launches into Digital Marketplaces
Posted 6-Nov-2019 11:42


Kordia offers Microsoft Azure Peering Service
Posted 6-Nov-2019 11:41


Spark 5G live on Auckland Harbour for Emirates Team New Zealand
Posted 4-Nov-2019 17:30


BNZ and Vodafone partner to boost NZ Tech for SME
Posted 31-Oct-2019 17:14


Nokia 7.2 available in New Zealand
Posted 31-Oct-2019 16:24


2talk launches Microsoft Teams Direct Routing product
Posted 29-Oct-2019 10:35


New Breast Cancer Foundation app puts power in Kiwi women's hands
Posted 25-Oct-2019 16:13


OPPO Reno2 Series lands, alongside hybrid noise-cancelling Wireless Headphones
Posted 24-Oct-2019 15:32


Waikato Data Scientists awarded $13 million from the Government
Posted 24-Oct-2019 15:27


D-Link launches Wave 2 Unified Access Points
Posted 24-Oct-2019 15:07


LG Electronics begins distributing the G8X THINQ
Posted 24-Oct-2019 10:58


Arlo unveils its first video doorbell
Posted 21-Oct-2019 08:27


New Zealand students shortlisted for James Dyson Award
Posted 21-Oct-2019 08:18



Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.


Support Geekzone »

Our community of supporters help make Geekzone possible. Click the button below to join them.

Support Geezone on PressPatron



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.