• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract text from a cell

B

begcar

New Member
Hi everyone!
I´ve been fighting with excel for 2 days, and cannot find the solution for this...
From cell D2 to D526, I have different text some of them have 20 characters and some others has 300, the text is totally different, between all the cells.
all of my cells has: Country: and the name of the country; for example: D2, has: Country: USA, D3, has: Country: Dubai, D4: Country: Ecuador; D5: Country: Russia Fed., so every single cell contains, the name of one country and the other text; like this:John Smith, 11529, Cypress, 520, Washington DC, , December, Wed., 25, 2013, Promotion, Recepcionist II, USD $1,000.00, John Johnson, This is approved by Karen Smith. John has been contribuiting with his xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx,Country: USA, Karen Smith, Since John has not been promoted......................, ETC...
or can be: John Smith, 11529, Cypress, 520, Washington DC, USD $1,000.00, Karen Smith, Country: Canada.
So that´s why every cell has different number of characters, and the Country is in a different place, also the country can have 1 or 2 words, what I need is to extract just the country and the name, this should go to column G accordingly, (from G2 to G526), so I need: that column G, states: Country: USA, Country: Dubai.
I tried text to columns, but is too manual, since there are a lot of characters, I am looking for something more automate; and I am not good with VBA! So you help will be highly appreciated! Thanks!
 
=CONCATENATE("Country ", MID(A1,FIND(":",A1,1),8))
give this a try? could be to simple, so may not work for you but may be close

=MID(A1,FIND(":",A1,1)-7,17)
this may be better, but need to figure out the last argument ("17") to be dynamic to find the length of the countrys name (the way it is right now with 17 it will only grab the next 17 characters... obviously thats not going to work.... hmmm


can you upload a sample workbook?
 
Last edited:
begcar said:
Hi everyone!
I´ve been fighting with excel for 2 days, and cannot find the solution for this...
From cell D2 to D526, I have different text some of them have 20 characters and some others has 300, the text is totally different, between all the cells.
all of my cells has: Country: and the name of the country; for example: D2, has: Country: USA, D3, has: Country: Dubai, D4: Country: Ecuador; D5: Country: Russia Fed., so every single cell contains, the name of one country and the other text; like this:John Smith, 11529, Cypress, 520, Washington DC, , December, Wed., 25, 2013, Promotion, Recepcionist II, USD $1,000.00, John Johnson, This is approved by Karen Smith. John has been contribuiting with his xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx,Country: USA, Karen Smith, Since John has not been promoted......................, ETC...
or can be: John Smith, 11529, Cypress, 520, Washington DC, USD $1,000.00, Karen Smith, Country: Canada.
So that´s why every cell has different number of characters, and the Country is in a different place, also the country can have 1 or 2 words, what I need is to extract just the country and the name, this should go to column G accordingly, (from G2 to G526), so I need: that column G, states: Country: USA, Country: Dubai.
I tried text to columns, but is too manual, since there are a lot of characters, I am looking for something more automate; and I am not good with VBA! So you help will be highly appreciated! Thanks!
Click to expand...
Sorry I missed one part, can be also 1500 characters, so it can be from 10 to 3000 it depends.....
 
IF, and only if.... "Country: ______" is the only part in each cell that contains the colon symbol, we may be able to use the Text-to-Column tool. set the delimiter as the colon. that would seperate each cell/line into 2 columns; the 1 ending with "Country:" and the second starting with the name of said country. then you could use the CONCATENATE function nested with a LEFT and RIGHT Function (and probably some FIND fucntions as well.... but that may work! !! but again, IF AND ONLY IF that is the only time the colon is used.... which may very well not be the case.


sample file by chance or perhaps provide a few more example lines?


EDIT: i just tried this above method and it works... after splitting the text into columns, i used this =CONCATENATE(RIGHT(A1,7),": ",LEFT(B1,FIND(",",B1,1)-1))
 
Last edited:
well i quickly see that my text-to-columns idea wont work.... dang!

i'll see if i can figure something out (but i'm not a vba whiz either)
 
Last edited:
=CONCATENATE(MID(C4,FIND("Country:",C4,1),8)," ",MID(C4,FIND("Country:",C4,1)+9,3))

Im still hitting a wall of figuring out a way to get his last argument to be dynamic based on the following listed country....


any chance you have a list of all possible countries that may be listed?


I THINK I GOT IT!!!! i had to use some helper columns, so i'm trying to eliminate them.
 
Last edited:
=CONCATENATE("Country: ",MID(D4,FIND("Country:",D4,1)+9,((FIND(" ",D4,FIND("Country:",D4,1)+9))-(FIND("Country:",D4,1)))-9))

D4 is the cell with the line of text
 
This is great json, I add this formula into my original file, it works almost perfect, since I have one that the country is Rep of Congo, and as soon as I put the formula it just gets: Rep of. Is there any way to do not have those?
 
hmmm.... that could make it pretty tricky.... i didn't think about countries with muliple names..... wow


do you have any other countries that have multiple words in the name like Rep of Congo?

if not, it would probably be easier to just do a work around with the Find & Replace tool.... but that also wouldnt fix the issue.... especially if you have another country whose name consists of 2 or more words...


wow... you might have got me on this one!! hahaha


EDIT: with the formula approach that i started, i dont know of any way to dynamically determine the correct length of a country's name when it contains a space......

if you had a list of all country names we may be able to use a lookup function instead of our current approach.....? ...but i just saw you say that you dont have one.... dang

sorry, i'm not sure to do now.....
 
Last edited:
Yes I have more... like Russian Federation, United Arab Emirates, United Kingdom, Saudi Arabia, El Salvador,... etc, and Unfortunately I do not have a list with all these countries :( I asked for one also!
 
Gooday begcar
Can you not use the names you have to start a list and so do as json has suggested and use a lookup.
If you turn the list into a table the lookup will become dynamic and include new names added to the table.
 
Hi bobhc,
Unfortunately I do not have a list with all the countries, since this an example maybe inside the system, where the report comes can have more countries, that the ones that I have in this report, that is why I was wanting something that no matter the country, can bring it----
 
Just a blind shot..

=MID($A4,FIND("Country: ",$A4)+9,FIND("Employee Group:",A4)-(FIND("Country: ",$A4)+9))
 
Hello Begcar,

Based on your sample file, all the cells commonly have 'Country:' then after 'Employee Group', so try this in B4 & copy down.

=TRIM(MID(A4,SEARCH("Country:",A4)+8,SEARCH("Employee Group",A4)-SEARCH("Country:",A4)-8))
 
Hi, begcar!
I stick to both Debraj(ex-Roy) and Hasseb A formulas; they work fine with all posted and other manually edited more strange country names.
Regards!
 
not ALL of the lines have "Employee Group" right after the Country

correction: some of the lines have "employee group" multiple times, but that wouldnt matter... so yes, this could work. *fingers crossed that all lines are consistent!!
 
Last edited:
Yeah!!!!!!!!!!!!!!!!!!!!!! You guys are amazing!!!!!!!!!!!!! I need some classes with you!!! but can anyone help me how did you get this? MID($A4,FIND("Country: ",$A4)+9,FIND("Employee Group:",A4)-(FIND("Country: ",$A4)+9)) I cannot understand very well the formula, but this is great!!!! THANKS THANKS THANKS1
 


MID(text,Start Num, num chars)
This funciton needs to know what text to look at, what character number to start with, and how many characters you want returned.

Lets look at a simple example. pretend that A1 contains: "Chandoo.org is the bestest"
the function: MID(A1,4,7) would go to A1, start at the 4 char pos and return 7 chars, resulting in "ndoo.or". The limititation of this is that it will ONLY and always go to the 4 char pos and ONLY and ALWAYS return 7 chars.

this wouldnt work for us because the country names are in random locations and different lengths.


MID($A4,FIND("Country: ",$A4)+9
If the funciton said: MID(A4, "Country:_", 4) it would look in A4 for the string "Country:_" and return 4 characters---'Coun'

but because our country names show up in random places, we cant use such static arguments.

FIND("Country: ",$A4)+9: The FIND Fucntion returns a character number that we want to use for the "start num" argument for the MID function. This will look for "country: ", in A4. (lets say this returns 250 because the 'C' in Country was found in the 250 character). but then we want to add 9 to this number because we want it to start after "Country:_" where the actual county name starts instead of at char. 250.

FIND("Employee Group:",A4)-(FIND("Country: ",$A4)+9))
this last part i just stumbled upon unknowingly. This is basically finding the length of the Country Name. Since we know that Employee Group seems to follow the country name we are asking it to FIND the position where 'Employee Group' starts... (lets just say its in 255. Then we say find "country:_" [but add 9 to that again so that it starts at the end of "Country:_"... (lets say it finds 250 again) You'll notice that we are subtracting these 2 numbers returned from the 2 FIND functions.. so the first function will return 255 and the second funciton will return 250.. giving us the length of 5. The great thing about this is that it is dynamic...

lets pretend that "country:_" is in char. pos 250 and Employee Group:_" is in 265 for the following---

MID($A4,FIND("Country: ",$A4)+9,FIND("Employee Group:",A4)-(FIND("Country: ",$A4)+9))
---MID(A4,250+9,(265-(250+9))
-------MID(A4,259,(265-259)
-----------MID(A4,259,6) = Look in A4, start at char. pos. 259, return 6 characters.

Using the FIND function to find and return the char. positions make it very dynamic... it doesn't matter if "Country:_" is in char. pos. 210, 100, or 1228... or if the name of the country is 3,15, or 100 chars long.

but only potential downfall is if you have a line where "Employee Group does not immediately follow the Country name.


does that make more sense?


dont be afraid to play around with these to better see/understand how they work and how you may be able to utilize them in different ways!!
 
Last edited:
Hi @jason

I like the way.. you elaborate.. :)
just fYI.. there is a thin line between Perfection & Perfection with Experience..
both the above post.. mine & @Haseeb A are posted at a same time.. but.. there is a huge difference between both formula..

* Elaborated one is Case Sensitive.. where Haseeb's one is feel-free-to-use.
* Elaborated one may contain few more spaces than required.. where Haseeb's one is perfectly-trimmed-face.

If i have to elborate.. I will obviously choose Hasseb's one.. :)
 
You must log in or register to reply here.
Back
Top