• 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.

Need help with nesting 3 formulas in one

In one column I have

1BB4553 Altran Altran Emirat Arabe (434)
1BB4588 Altran Altran Austria (387)
1BB4594 Altran Altran Belgium (006)

I have put up three formula as I only need to get the name of the country, for 1st one "Emirat", for 2nd one "Austria", for 3rd one "Belgium".

Mentioned below are the formula that gives me individual answer, but I am not able to apply one formula to copy throughout all the rows and get the correct answer.

For 1st one : =LEFT("Emirat",SEARCH("Emirat",I4,1))
For 2nd one: =LEFT("Austria",SEARCH("Austria",I5,1))
For 3rd one: =LEFT("Belgium",SEARCH("Belgium",I6,1))

Please help.

Thank you in advance.
 
Also consider using Flash Fill

 
How about something like
= MID(data, 23, SEARCH(" ", data, 23) - 23)

View attachment 75300
Hi Peter,

Thank you for taking the time to help me, unfortunately the formula you gave me is not working cause 23 is not constant throughout in my data. So I would have to use the Search"country name".

For example:
1BB4569 Altran Altran Technologies France (001)
1BB4583 Altran Altran Innovations Spain (U005)
1BB4516 Altran Altran Service Germany (354)
 
In that case knowledge of what constitutes a valid country name needs to be built into the formula.

75308

SEARCH will do this, but then it remains to pick out the success from the array of failures. I would most likely use FILTER for this but IF and CONCAT would also work. On legacy systems (I hesitate to say 'obsolete') you may need to use LOOKUP to pick out the TRUE from a list of FALSE.
 

Attachments

  • ExtractCountry.xlsx
    12.3 KB · Views: 4
@Hui
You persuaded me to use Flash Fill for the first time ever! It worked fine but I would observe that I have never followed up the Microsoft AI offerings, though I have used corresponding functionality within PowerQuery (add column by example).

Then again, I have never used a filter button on a Table or from the ribbon (unclicking table filter buttons and stripes is now a reflex action). On occasion I have used the Advanced Filter with the extract option set and I use the FILTER function frequently.
 
  • Like
Reactions: Hui
@Hui
You persuaded me to use Flash Fill for the first time ever! It worked fine but I would observe that I have never followed up the Microsoft AI offerings, though I have used corresponding functionality within PowerQuery (add column by example).

Then again, I have never used a filter button on a Table or from the ribbon (unclicking table filter buttons and stripes is now a reflex action). On occasion I have used the Advanced Filter with the extract option set and I use the FILTER function frequently.

@Peter

For one off jobs, Flash Fill has became my goto tool,
99% of the time it is very clever and can extract quite complex data patterns
Fixing up a few outliers is normally quicker than a custom formula

But I still use Formula's especially for more repetitive jobs
 
In that case knowledge of what constitutes a valid country name needs to be built into the formula.

View attachment 75308

SEARCH will do this, but then it remains to pick out the success from the array of failures. I would most likely use FILTER for this but IF and CONCAT would also work. On legacy systems (I hesitate to say 'obsolete') you may need to use LOOKUP to pick out the TRUE from a list of FALSE.

You are awesome.

Thank you so much, it worked for me perfectly
 
Also consider using Flash Fill


Thanks Hui. I shall try this for sure.
 
Back
Top