Need help with nesting 3 formulas in one

[Fernandez30]

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.

 

[Peter Bartholomew]

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

 

[Hui]

Also consider using Flash Fill

Using Flash Fill in Excel - Microsoft Support

Have Excel automatically fill your data when it detects a pattern. For example, the months of the year, the days in a month, or a sequence of numbers. You can also parse data, like splitting first and last names out of a column, or concatenate data, like joining first and last names from two...

support.microsoft.com

 

[Fernandez30]

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)

 

[Peter Bartholomew]

In that case knowledge of what constitutes a valid country name needs to be built into the formula.



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.

 

[Peter Bartholomew]

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

 

[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

 

[Fernandez30]

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

 

[Fernandez30]

Also consider using Flash Fill

Using Flash Fill in Excel - Microsoft Support

Have Excel automatically fill your data when it detects a pattern. For example, the months of the year, the days in a month, or a sequence of numbers. You can also parse data, like splitting first and last names out of a column, or concatenate data, like joining first and last names from two...

support.microsoft.com

Thanks Hui. I shall try this for sure.