How to Convert Text in a String

Good Day!

I have been trying to find the best way to refine strings of text to something that is easily used in other programs (HD, P6, SAP). The goal is to have a standardized formatting and a string length that is acceptable (P6 does not accept long string lengths).

Ex. (you may also look at the attached excel file)

ORIGINAL TEXT
A2 = " Cut to Fill - Excavate, Load, Haul, Place, Level And Compact suitable common material;"

TRIM TEXT
C2 =TRIM(A2) = "Cut to Fill - Excavate, Load, Haul, Place, Level And Compact suitable common material;"

PROPER TEXT
D2 =PROPER(C2) = "Cut to Fill - Excavate, Load, Haul, Place, Level And Compact Suitable Common Material;"

SUBSTITUTE TEXT
This is where I get a little stuck. It works for one instance (Old Text = "Compaction", New Text = "Comp")

E2 =SUBSTITUTE(D2,substitute!$A$2,substitute!$B$2) = "Cut to Fill - Excavate, Load, Haul, Place, Level And Comp Suitable Common Material;"

BUT....I want it to read through a list of Old Text (Column A) and match that with the list of New Text (Column B). In this way I can have a seperate sheet of commonly used words to shorten. On my list I also have for example "Excavate" so that it will become "Exc"

I have been trying to nest a LOOKUP function, but haven't been able to work.

Thank you so much for your insight!

I can't read what your goal in your workbook, what exactly the results from original Text?

Hello Azumi

I am looking to apply multiple substitutions. The overall goal is shortening the length of my text by use of abbreviations. (I first applied simple formatting - trim and proper, the next stage is the substitutions.) I am able to do the single substitutions no problem, however there will be a list of substitutions needed.

If you were able to open the excel file I attached, you'll see that there is a list of "Old Text" and a list of "New Text" - I would like each row to substitute any word that appears on the list with the new word in that same list.

Not so bad with the sample list I have there (first sheet), but sometimes I will have hundreds of line items....eek

Thank you again!