String Replace with a pattern?

[Steve_K]

Hi all,

This is one of those cases where in a language like PHP, I'd get how to try and achieve what I want, but not quite in Excel. Here's the challenge I have:

[TABLE="width: 600"]
<tbody>[TR]
[TD]Abbreviations[/TD]
[TD]Category Names[/TD]
[/TR]
[TR]
[TD]TDS[/TD]
[TD]Tile Depot Store[/TD]
[/TR]
[TR]
[TD]PT[/TD]
[TD]Porcelain[/TD]
[/TR]
[TR]
[TD]WP[/TD]
[TD]Wood Porcelain[/TD]
[/TR]
[TR]
[TD]MP[/TD]
[TD]Metallic Porcelain[/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD]Porcelain Mosaic[/TD]
[/TR]
</tbody>[/TABLE]

From that, I'll build a replacement mechanism (like an explode(), implode(), and sprintf() in PHP), where I define a pattern of abbreviations which are delimited (in this case by "/"):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Category Structure[/TD]
[TD]Category Output[/TD]
[/TR]
[TR]
[TD]TDS/PT[/TD]
[TD]The Tile Depot/Porcelain[/TD]
[/TR]
[TR]
[TD]TDS/MP[/TD]
[TD]The Tile Depot/Metallic Porcelain[/TD]
[/TR]
[TR]
[TD]PM/MP/PT[/TD]
[TD]Porcelain Mosaic/Metallic Porcelain/Porcelain[/TD]
[/TR]
</tbody>[/TABLE]

and so on.

I've tried a few things and gotten some pieces of the puzzle, but I'm still struggling with comparing the category structure and generating the output based on the abbreviations and associated names. Here's what I know so far:

I know I can use JOIN to take a column of values and delimit them. For example,

=JOIN("/",B2:B6)

yields

Tile Depot Store/Porcelain/Wood Porcelain/Metallic Porcelain/Porcelain Mosaic

I also know I can use some form of INDEX and MATCH here, whereby, assuming the two datasets are one sheet, this:

=INDEX(Category_Names,MATCH(LEFT(A8,FIND("/",A8)-1),Category_Abbrevs,0))

yields "Tile Depot Store".

What I need to figure out is how to combine these two methods properly. (Or, as is often the case, figure out if there's a better way to think about this).

As always with this lovely group of people, thank you for taking a look and considering this. I appreciate any and all help very much.

 

[Rick Rothstein]

Assuming your "Abbreviation/Category Names" table is located on Sheet 1 in range A1:B6 (change in the code to encompass all of the items in your actual table), here is a UDF (user defined function) that should work...

Function SRWP(R As Range) As String
  Dim X As Long, Table As Variant, Abbrev() As String
  Static Dict As Object
  If Dict Is Nothing Then
    Set Dict = CreateObject("Scripting.Dictionary")
    Table = Sheets("Sheet1").Range("A1:B6")
    For X = 1 To UBound(Table)
      Dict.Item(Table(X, 1)) = Table(X, 2)
    Next
  End If
  Abbrev = Split(R.Value, "/")
  For X = 0 To UBound(Abbrev)
    Abbrev(X) = Dict.Item(Abbrev(X))
  Next
  SRWP = Join(Abbrev, "/")
End Function

By the way, the function name SRWP is short for String Replace With Pattern.


HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use SRWP just like it was a built-in Excel function. For example,

=SRWP(A2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Steve_K]

Thanks Rick, I really appreciate this. Unfortunately for me, I neglected to mention that I've got to use Google Sheets for this. This gives me a good idea how to start thinking about the JavaScript though. (Is there any chance you're able to translate this in JavaScript by chance?) I've got the tutorials up anyhow and will see what I can do if there's no formulaic solution. Thanks again for taking a look!

 

[NotC90]

Hi,

something like this should do the trick in Google Sheets:

[COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]JOIN[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"/"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]ArrayFormula[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]VLOOKUP[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]ArrayFormula[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]SPLIT[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]A12[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"/"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]true[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#7E3794][FONT=Inconsolata]$A$2:$B$6[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]false[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR]

 

[Rick Rothstein]

Thanks Rick, I really appreciate this. Unfortunately for me, I neglected to mention that I've got to use Google Sheets for this. This gives me a good idea how to start thinking about the JavaScript though. (Is there any chance you're able to translate this in JavaScript by chance?) I've got the tutorials up anyhow and will see what I can do if there's no formulaic solution. Thanks again for taking a look!

Sorry, I don't know JaveScript. Just so you know, the Static variable Dict retains whatever is assigned to it for as long as Excel is open (more technically, for as long as the module it is in is active). That way, I only have to create and load up the dictionary one time for the entire time the workbook is open no matter how many times the UDF is used or recalculated. I don't know if JavaScript has such a construction (I am betting it does) but I thought I would explain for you in case you were not familiar with Static variables. As for the dictionary object, I am almost positive JavaScript can use it. The only other things you will need to find in JavaScript is the equivalents for the VBA Join and Split functions... I believe everything else I did in my code is fairly routine and should be easy to translate into JavaScript.

 

[Steve_K]

@NotC90: Wow, this works amazingly, thank you so much.

@Rick: Thank you for sharing your knowledge, and for the detailed explanation. I've got basic understanding of VBA and didn't know about the dictionary object at all. It's great to learn something new.