Find and Replace Text with SUBSTITUTE formula

I have this range of data, which is a takeoff of engineered wood products. The number of Columns are fixed, but the start of the range can occur on different Row numbers. What I would like to do is create a macro that will Find and Replace All the three different values (“-“, “STD.” and “1/16””) in Column A (“MARK”) with a new abbreviated mark, based on the values of Column D (“DIMENSIONS”) and Column E (“GRADE”).

I would like the new abbreviated mark to follow this nomenclature:

For all occurrences of “-“ or “STD.”, the new abbreviated mark should read:

“Abbrev. Dimensions of the beam + Abbrev. Grade”
Dimensions (only includes the whole number from the width and height of the beam)
3 1/2” X 11 7/8” = “311”

Grade:
2.0E = “LVL”
1.55E = “LSL”
30F-E2 = “BB”
24F-V4 = “GLB”

Example for Cell “A4” = “311LVL”

For all occurrences of “1/16””, the new abbreviated mark should read:
“Abbrev. Dimensions + “HDR”

Example for Cell “A8” = “511HDR”


I first approached this by trying to use the SUBSTITUTE function because I knew I’d be able to utilize and manipulate the existing text to create the new abbreviated mark that I wanted.

So with SUBSTITUTE nested several times, I combined the cell values from Column D and Column E, and removed all spaces, fractions with the inches symbol (in this data set those are quotation marks), and replaced the grade with the abbreviated type. This formula produces the result I want.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D13&E13," ",""),"X",""),"""",""),"1.55E","LSL"),"2.0E","LVL"),"24F-V4","GLB"),"30F-E2","BB"),"1/4",""),"1/2",""),"3/4",""),"7/8","")

The issue with this formula, is that it needs to reference a cell address (in this example, "D13&E13"), which as I mentioned above, the start of the range can occur at different Row numbers because it's entirely dependent on how much material is listed in a given project.

I was unsuccessful in trying to record this macro, and I don’t know where to start to write this out in VBA. Is it possible to use Substitute and have it reference the address based on the ActiveCell address yielded by Find?

Using SUBSTITUTE nested seems really convoluted, but as you can imagine there are so many potential combinations of beam types and beam dimensions. This seems like the simplest way to account for all the potential combinations.

I'd really appreciate any suggestions or input on this. Thanks in advance.

"HDR" is abbreviated for "Header". This is also a type of beam but it's specifically located over doors and windows.

In order for this formula to work, it still needs a cell reference?

VBA

Please Login or Register  to view this content.

Thank you for responding. I tested this macro out, and it seems to work only if the range of data starts at Row 3. Is there a way for this macro to work within any row, and also possibly loop if there is a blank row inserted separating the data?

Change to

Please Login or Register  to view this content.

This is brilliant, thank you so much!

jindon,

I'm now getting another now Run-time error 9: Subscript out of range. It's highlighting:

a(i, 1) = Val(Split(x(0))(0)) & Val(Split(x(1))(0)) & myGrade

I think the error is occurring because there are additional rows above (or below) with "-" in Column A that don't match "myGrade"? Is there a way to modify the code to skip/ignore all other values in Column E , other than what's specified with myGrade?

You are now asking almost different question.
You need to upload a workbook showing before/after and the reason.

So I thought about it, and I realized the easiest way to work around this was to replace all "-" to "--" in Column A IF in Column B = "LP LSL BEAM" OR "LP LVL BEAM". I adjusted your VBA code to search for "--" instead of "-".

Please Login or Register  to view this content.

So far this seems to be working. Do you think this makes sense?