Take a text string and parse out and result in defined abbrevations

[smjbill]

Hi all,
I am not sure what it would take to accomplish this in excel so apologies if this is not in the correct forum.

Lets assume there is a "user input" field in one worksheet, this field then looks at another sheet which has a list of potential matches (for the full word group or single words) in one column that is accompanied by another column with its defined abbreviation. (example below)

Also any space in the user input field should be replaced with a _ (underscore). I understand looking at each word in this case would not work as the "Air Handling Unit" is 3 words but the set should result in AHU. so this adds to the complexity. As it should look for a match for the whole set in parts and resolve the individual words remaining essentially.

Sheet 1
Column A
User Input
Air Handling Unit Airflow Actuator

Column B
Result
AHU_AF_ACTR

Sheet 2
Column A Column B
Full Name Abbreviation
ACTUATOR ACTR
AIRFLOW AF
AIR HANDLING UNIT AHU
ALARM ALM
AMPERAGE AMP
AVERAGE AVG
BUILDING BLDG


What would be needed to achieve this functionality?
I am using Excel 2010 but have access to newer versions as well.
Screenshots attached.
Greatly appreciate any help.
2016-10-12_21h56_22.png2016-10-12_21h57_41.png

[Olly]

Try this:

Please Login or Register  to view this content.

Use as a worksheet function - in Sheet1!B2, enter:

Please Login or Register  to view this content.

[smjbill]

Thanks Olly,
Where/how would I place this code? Sorry fairly new to advanced excel functions.

As I understand, this is some VBA, I've attached the xls that is now a xlsm to allow for VBA use. And used this https://www.ablebits.com/office-addi...a-macro-excel/ to insert a module with the code you provided, it appears to run as Abbreviate. But when using the =Abbreviate(A2) I get an error stating "Abiguous name detected: Abbreviate"

I've attached this file as noted.

[Olly]

You have two procedures named 'Abbreviate' - a Sub in Module 1, and a Function in Module 2.

Simply delete Module 1, and your workbook works as intended.

[smjbill]

Excellent, I missed that I left a remnant of me fiddling around as another module.
Thank you very much for your help!

[Olly]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. You can also Add Reputation to users who have helped you. Thanks.

[smjbill]

Does anyone happen if any cloud based OneDrive type sites support a xlsm file? I want the ability to actively share this to a team who can add to Sheet2's Dictionary of Names and the associated Abbrevation.

[Olly]

You can open .xlsm files from OneDrive in Excel - but macros will not work in Excel Online.

[smjbill]

Double post. sorry

[smjbill]

Got it, if i were to enhance this further, if a user adds a new definition to the library in sheet 2, how can I make it so it sorts the first Column A-Z and automatically keeps the adjacent cell with its abbreviation "linked" so the second column does go out of order.

Nevermind, this is a not an issue other then its not automatically done.

[smjbill]

It appears this is running into issues where its not trying to find a "full name' match first and it ends up breaking the name out into the individual name definitions..I've attached screenshots how its not trying to find "Secondary Chilled Water Return Pressure" in it's full form which is defined with a abbreviation of SCHWR_PRS, and instead is taking the partial word match and piecing it together to be "SECONDARY_CHWR_PRS"

Is there a way to add a step to look for a full match first and use that abbreviation?

[smjbill]

I would also like to add that in addition to spaces, any dashes also are replaced with _ (underscores).

[Olly]

For some reason, the forum software won't let me post the revised code inline. See attached text file for updated function.

[smjbill]

Hi Olly,
Just got a chance to review this. Looks great, but I am still having a few incidents where it won't quite search for full matches, for example if there is a word in-front of OUTSIDE AIR TEMPERATURE COMPARISON like AH-1 OUTSIDE AIR TEMPERATURE COMPARISON. Rather then converting to AH_1_OATCPR, it does AH_1_OA_T_COMPARISON. OUTSIDE AIR TEMPERATURE COMPARISON exists in the dictionary as OATCPR.Capture.JPG

[smjbill]

Would appreciate any thoughts on a fix for this. I've attached the latest verison.

[Pepe Le Mokko]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

http://www.excelguru.ca/forums/showt...d-abbrevations
http://www.vbaexpress.com/forum/show...d-abbrevations

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).