LinkBack URL
About LinkBacks
How to extract the major company names (without (HK), "Eng", "Co Ltd", etc) with same formula?
e.g.
ABC (HK) Ltd
DEF GH (China) Ltd
IJ KL MN Eng Ltd
OPQ Co Ltd
R-S Construction Ltd
to the following:
ABC
DEF GH
IJ KL MN
OPQ
R-S
How to extract the major company names (without (HK), "Eng", "Co Ltd", etc) with same formula?
e.g.
ABC (HK) Ltd
DEF GH (China) Ltd
IJ KL MN Eng Ltd
OPQ Co Ltd
R-S Construction Ltd
to the following:
ABC
DEF GH
IJ KL MN
OPQ
R-S
Hi London,
Given the examples above, it doesn't look to be a way to do it with a single formula. Maybe one of the smart Guru's will see something I don't. Can you supply more examples that might help?
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
I've got a formula but Sucuri is blocking me out. Let's see if this gets through.
Nope. Can't post formulas or headers. There are no greater / lesser than operators in the formula either.
Last edited by FlameRetired; 09-21-2017 at 11:54 PM.
Dave
Dave, attach txt file with formula inside
Capital idea!
Can't post the formula but let's try WB.
Last edited by FlameRetired; 09-22-2017 at 12:01 AM.
You are welcome my friend
Or try:
=LEFT(A1,FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))-1)
@Phuocam
Very clever.
How to revise formula to get abbreviation of company name?
Thanks for your reply.
Actually the company names include:
ABC Ltd
ABC (Fast East) Ltd
DEF GH JKK Construction (China) Ltd
IJ KL MN Eng Co Ltd
PP QQ Eng & Consultants Ltd
SS TT Roller Shutters Ltd
UU VV Waterproofing Eng Co
that the result of the formula (remove last 2 wordings of the above names) is
#VALUE!
ABC (Fast
DEF GH JKK Construction
IJ KL MN Eng
PP QQ Eng &
SS TT Roller
UU VV Waterproofing
My expected result, with abbreviation of company names, is:
ABC
ABC
DEF GH JKK
IJ KL MN
PP QQ
SS TT
UU VV
(i.e. remove wording at right of likes (China), Construction, Eng Co Ltd, Roller Shutters, Waterproofing
or any wordings that can be added in formula later)
Last edited by london7871; 09-22-2017 at 03:52 AM. Reason: adding "at right of " at i.e.
Hey London,
So are you still looking for a single formula or did the one given above work?
I still look for single formula.
Try this array formula
Enter in B1 and copy down
Formula:
Please Login or Register to view this content.
***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
v A B 1 ABC Ltd ABC 2 ABC (Fast East) Ltd ABC 3 DEF GH JKK Construction (China) Ltd DEF GH JKK 4 IJ KL MN Eng Co Ltd IJ KL MN 5 PP QQ Eng & Consultants Ltd PP QQ 6 SS TT Roller Shutters Ltd SS TT 7 UU VV Waterproofing Eng Co UU VV
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Thanks AlKey.
In practise as I want to get the abbreviation of company name,
how do I get the abbreviation upto and excluding the following four wordings with one formula?
Construction, Eng, Co, Ltd
(preferably not to use array formula as prefix will be added later in the formula)
My expected result:
Abc Ltd
Def Gh Jkk Construction (China) Ltd
Ij Kl Mn Eng Co Ltd
by formula:
Abc
Def Gh Jkk
Ij Kl Mn
Hi london7871,
Your requirements keep changing every time you post. I, personally have no intention to entertain you any further with these fake company names as this is just the waste of my time.
Good luck.
Last edited by AlKey; 09-24-2017 at 09:19 AM.
Sorry Alkey.
I will improve my skill of query next time.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks