Excel Problem - Assistance Please

Forums › Desktop computing › Excel Problem - Assistance Please

networkn

Networkn
32349 posts

Uber Geek

ID Verified

Trusted

Lifetime subscriber

#217892 17-Jul-2017 14:47

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.

Oblivian

7296 posts

Uber Geek

ID Verified

#1823871 17-Jul-2017 15:37

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/

nutbugs

269 posts

Ultimate Geek

Lifetime subscriber

#1824002 17-Jul-2017 19:04

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

freitasm

BDFL - Memuneh
79250 posts

Uber Geek

Administrator

ID Verified

Trusted

Geekzone

Lifetime subscriber

#1824038 17-Jul-2017 20:13

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

Hammerer

2476 posts

Uber Geek

Lifetime subscriber

#1824039 17-Jul-2017 20:17

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

networkn

Networkn
32349 posts

Uber Geek

ID Verified

Trusted

Lifetime subscriber

#1824310 18-Jul-2017 12:19

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.

Oblivian

7296 posts

Uber Geek

ID Verified

#1824318 18-Jul-2017 12:29

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.

Hammerer

2476 posts

Uber Geek

Lifetime subscriber

#1824976 19-Jul-2017 09:16

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.

Equinox IT

Cloud spending continues to surge globally, but most organisations haven’t made the changes necessary to maximise the value and cost-efficiency benefits of their cloud investments. Download the whitepaper From Overspend to Advantage now.

networkn

Networkn
32349 posts

Uber Geek

ID Verified

Trusted

Lifetime subscriber

#1825282 19-Jul-2017 14:49

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.