Thread: how to automatically extract country name from address in excell 2007

"School of Engineering, Swinburne University of Technology, Malaysia; Large Millimeter Telescope, National Institute for Astrophysics, Optics and Electronics, Mexico; School of Electronic, Electrical and Computer Engineering, University of Birmingham, United Kingdom; LG Electronics Institute of Technology, Seocho-Gu, Seoul 137-724, South Korea; School of Electrical Engineering and Computer Science, Seoul National University, Seoul 137-724, South Korea; School of Electrical and Electronics Engineering, Chung-Ang University, South Korea"

i have problem here actually all of the word above are adress and each address separate by ";"..and all the words above are in one columns..i need to separate all of this..
this is my sample of data.. there are about 7k-8k like this type of data, i need to make this in their category for example it need to be like this

column1=department
row1=School of Electrical Engineering and Computer Science

column2=university
row1column2=Seoul National University

column3=country
row1column3=South Korea

Two options come immediately to mind. One is a vba solution that will take me a few minutes to put together and will only be good if you are going to be re-using this solution over and over again. If this is a one-time problem then I would advise the following:

1) Open the list in Microsoft Word
2) Click on Ctrl + H (which will bring up the replace pop-up window)
3) Find: "; " (without the quotation marks)
4) Replace with: "^p" (again without the quotation marks)
5) Save the file as a text file and open in Excel
6) When opening using the delimiter as comma and it will separate the department, university, and country into three columns
7) If they are all in one column then use the Text-to-Columns option under the Data Tab.

Hope this helps.

abousetta

Hi,

Please confirm if first two commas will have only department
after this will have university and
after this will have Countrly
always in each record.

e.g., dep1, dep2, university, country

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi,

Your data is inconsistent and therefore any solution will require you to visually check each row to make sure it is correct. There is a very old saying "garbage in, garbage out". If the original data is inconsistent then the end results will be consistent.

Is there a pattern that I am not seeing? If so, what it is it?

abousetta

Is the original data a text file or an excel sheet? If you could show us the original file and what you want to do with the inconsistencies then we should be able to help. For example, what do we do when there are two departments? Do you want to lines one for each department or how do you wan the data to appear.

abousetta

The data you are getting is horribly laid out. Perhaps you can ask whoever it is that prepares those files to do so in a workable manner. As it is now, you may have to manually check and split your data. I couldn't even perform text to columns with that data. I can't imagine the code that would need to be written to perform the tasks you require. That's just my opinion and it may not be worth much in this regards.

I think these 3 formulas get you 98% of the way there, then you can eyeball your way through and fix the errant few.

B2: =LEFT(A2, FIND(",", A2)-1)
C2: =TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",100)), 99, 100))
D2: =TRIM(RIGHT(SUBSTITUTE(A2, ",", REPT(" ",100)), 100))

Copy those three down.

This is done after a TEXT TO COLUMNS on the semicolons split the data out into individual groups.

Originally Posted by Mordred

The data you are getting is horribly laid out. ... That's just my opinion ...

Spot on, IMHO - just look at line 6225 for inconsistency.