Many Substitutes on single cell of text

Blacksmith

Blacksmith

New Member
Joined
Jan 6, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hello all,

First poster and total excel novice here. I've recently been put in charge of a filing system at my workpace, and the software which was previously used was massively out of date and required lots of repetitive work from users. Over the past few weeks I've been iterating on an Excel-based replacement system that does a lot of the repetitive on its own, and learning how formulae work in the process.

One of the tasks I would like the system to do is automatically replace many abbreviations in a single cell of input text. A lot of the internal records at my workplace use extensive abbreviations, but when they need to be sent to insurance carriers, the insurance carriers demand all of the abbreviations be removed and re-written longform. What I've devised to fix this situation is a great many nested substitute commands, referencing a list of abbreviations and a list of their full texts in a separate sheet. The problem is, I used as many nested commands as Excel would allow, and I'm already very nearly approaching the maximum amount of abbreviations this system can handle as a result.

I would like to rework this system to substitute from a list of abbreviations of arbitrary length, not limited by the max number of nested commands. Is this possible? Maybe with a named range? All help or advice is appreciated.

(I have attached images showing examples of the nested substitute commands and some of the abbreviation list they reference)
 

Attachments

  • Nested Commands.PNG
    Nested Commands.PNG
    33.2 KB · Views: 28
  • Abbreviation list.PNG
    Abbreviation list.PNG
    4.4 KB · Views: 28

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Something like this.

Book1
AB
1re Legal Stuff The IC of the PL did stuff to the CL accourding to the OPC. Sent up for REV.re: Legal Stuff The insurance carrier of the Plaintiff did stuff to the client accourding to the opposing counsel. Sent up for Review.
Sheet5
Cell Formulas
RangeFormula
B1B1=RREPLACE(A1)


VBA Code: 
Function RREPLACE(s As String)
Dim LK(0 To 5, 0 To 1)
LK(0, 0) = "re"
LK(0, 1) = "re:"
LK(1, 0) = "IC"
LK(1, 1) = "insurance carrier"
LK(2, 0) = "PL"
LK(2, 1) = "Plaintiff"
LK(3, 0) = "CL"
LK(3, 1) = "client"
LK(4, 0) = "OPC"
LK(4, 1) = "opposing counsel"
LK(5, 0) = "REV"
LK(5, 1) = "Review"

For i = 0 To UBound(LK)
    s = Replace(s, LK(i, 0), LK(i, 1))
Next i

RREPLACE = s
End Function
 
Upvote 0
VBA Code: 
Private Sub ReplaceAbbreviations(rg As Range)
Const sList = "re:,re,insurance carrier,IC,Plaintiff,PL,client,CL,opposing counsel,OPC,review,REV"
Dim sArray() As String, i As Long
sArray = Split(sList, ",")
For i = LBound(sArray) To UBound(sArray) Step 2
rg.Replace what:=sArray(i + 1), replacement:=sArray(i), LookAt:=xlPart, MatchCase:=True
Next i
 End Sub

Sub ReplaceAbb()
Dim rg As Range
Set rg = Range(Cells(1, "A"), Cells(10, "A"))
ReplaceAbbreviations rg
End Sub
 
Upvote 0
Thank you for the responses, I haven't touched VBA at all yet, but I've used a few other similar languages so hopefully I should be able to figure it out. Don't often have enough time to work on my system but I will report back when I get the chance.
 
Upvote 0
Welcome to the MrExcel board!

I think more clarification is needed.
  1. Why do you have two REV in the list?
  2. Are the replacements always case-sensitive? Perhaps this relates to Q1 about the two REV's. But what results would you expect for original data of ..
    a) This CL is female
    b) CL is to return papers today
  3. How big is your actual list of abbreviations?
  4. Could we have a small set of sample dummy data AND the expected results in a form that we can copy/paste to test with? My signature block below has help with that.

On a very quick look at the suggestions so far, I don't think they will satisfy your need as both convert
"Let's replenish the sugar"
to
"Let's re:plenish the sugar"

Also, just a warning - this sort of text manipulation is often virtually impossible to get 100% what you want. I suspect any solution will still need careful manual chacking of the output.
 
Upvote 0
Pending the answers to the above questions, here is my initial suggestion. I have put everything on one sheet but that can easily be altered.
Abbreviations and replacements in EF
Original data in column A, code results in column B.

VBA Code: 
Sub Replace_Abbreviations()
  Dim RX As Object
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long
  Dim s As String
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  b = Range("E2", Range("F" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    s = a(i, 1)
    For j = 1 To UBound(b)
      RX.Pattern = "\b" & b(j, 1) & "\b"
      s = RX.Replace(s, b(j, 2))
    Next j
    a(i, 1) = s
  Next i
  Range("B2").Resize(UBound(a)).Value = a
End Sub

Book1
ABCDEF
1DataAbbrevFull
2re Legal Stuff The IC of the PL did stuff to the CL accourding to the OPC. Sent up for REV.re: Legal Stuff The insurance carrier of the Plaintiff did stuff to the client accourding to the opposing counsel. Sent up for review.rere:
3Let's replenish the sugar for the CLLet's replenish the sugar for the clientICinsurance carrier
4PLPlaintiff
5CLclient
6OPCopposing counsel
7REVreview
8
Sheet1
 
Upvote 0
Helper columns.
Each helper column converts some abbreviations.
The next column takes the result and converts more.
 
Upvote 0
Peter_SSs said:
Welcome to the MrExcel board!

I think more clarification is needed.
  1. Why do you have two REV in the list?
  2. Are the replacements always case-sensitive? Perhaps this relates to Q1 about the two REV's. But what results would you expect for original data of ..
    a) This CL is female
    b) CL is to return papers today
  3. How big is your actual list of abbreviations?
  4. Could we have a small set of sample dummy data AND the expected results in a form that we can copy/paste to test with? My signature block below has help with that.

On a very quick look at the suggestions so far, I don't think they will satisfy your need as both convert
"Let's replenish the sugar"
to
"Let's re:plenish the sugar"

Also, just a warning - this sort of text manipulation is often virtually impossible to get 100% what you want. I suspect any solution will still need careful manual chacking of the output.
Click to expand...


I popped in the XL2BB add-in, hopefully this works...
Book1
ABCDEFGHIJKL
1Matter NameDescriptionTimeDateTimekeeperTaskActivityClientMatterBillable
2RosenowAttend telephonic Status conference with IC re discovery status and settlement issues0.11/10/2020MWHL110A1061181251Y
3LandrumPrepare for DP of expert Michael Steingart, D.O. (REV reports, MR’s from chiropractor, surgeon and imaging reports over 300 pages) 1.2Total Hours:L330A1011181671Y
4LandrumTravel to/from Phoenix for DP of PL expert Michael Steingart, D.O. (Elise Smith advised by letter 12-11-19)3.86.7L330A1091181671Y
5LandrumAttend DP of expert Dr. Steingart in Phoenix1.6L330A1091181671Y
INPUT
Cell Formulas
RangeFormula
I2I2=INDEX(NOTOUCH!A2:D300,NOTOUCH!A2,3)
J2J2=INDEX(NOTOUCH!A2:D300,NOTOUCH!A2,4)
I3I3=INDEX(NOTOUCH!A2:D300,NOTOUCH!A3,3)
J3J3=INDEX(NOTOUCH!A2:D300,NOTOUCH!A3,4)
I4I4=INDEX(NOTOUCH!A2:D300,NOTOUCH!A4,3)
J4J4=INDEX(NOTOUCH!A2:D300,NOTOUCH!A4,4)
I5I5=INDEX(NOTOUCH!A2:D300,NOTOUCH!A5,3)
J5J5=INDEX(NOTOUCH!A2:D300,NOTOUCH!A5,4)
G2G2=IF(ISERROR(SEARCH(NOTOUCH!I2, B2)),IF(ISERROR(SEARCH(NOTOUCH!I3,B2)),IF(ISERROR(SEARCH(NOTOUCH!I4,B2)),IF(ISERROR(SEARCH(NOTOUCH!I5,B2)),IF(ISERROR(SEARCH(NOTOUCH!I6,B2)),IF(ISERROR(SEARCH(NOTOUCH!I7,B2)),IF(ISERROR(SEARCH(NOTOUCH!I8,B2)),IF(ISERROR(SEARCH(NOTOUCH!I9,B2)),IF(ISERROR(SEARCH(NOTOUCH!I10,B2)),IF(ISERROR(SEARCH(NOTOUCH!I11,B2)),IF(ISERROR(SEARCH(NOTOUCH!I12,B2)),IF(ISERROR(SEARCH(NOTOUCH!I13,B2)),IF(ISERROR(SEARCH(NOTOUCH!I14,B2)),IF(ISERROR(SEARCH(NOTOUCH!I15,B2)),IF(ISERROR(SEARCH(NOTOUCH!I16,B2)),IF(ISERROR(SEARCH(NOTOUCH!I17,B2)),IF(ISERROR(SEARCH(NOTOUCH!I18,B2)),IF(ISERROR(SEARCH(NOTOUCH!I19,B2)),IF(ISERROR(SEARCH(NOTOUCH!I20,B2)),IF(ISERROR(SEARCH(NOTOUCH!I21,B2)),IF(ISERROR(SEARCH(NOTOUCH!I22,B2)),IF(ISERROR(SEARCH(NOTOUCH!I23,B2)),IF(ISERROR(SEARCH(NOTOUCH!I24,B2)),IF(ISERROR(SEARCH(NOTOUCH!I25,B2)),IF(ISERROR(SEARCH(NOTOUCH!I26,B2)),IF(ISERROR(SEARCH(NOTOUCH!I27,B2)),IF(ISERROR(SEARCH(NOTOUCH!I28,B2)),IF(ISERROR(SEARCH(NOTOUCH!I29,B2)),IF(ISERROR(SEARCH(NOTOUCH!I30,B2)),IF(ISERROR(SEARCH(NOTOUCH!I31,B2)),"",NOTOUCH!J31),NOTOUCH!J30),NOTOUCH!J29),NOTOUCH!J28),NOTOUCH!J27),NOTOUCH!J26),NOTOUCH!J25),NOTOUCH!J24),NOTOUCH!J23),NOTOUCH!J22),NOTOUCH!J21),NOTOUCH!J20),NOTOUCH!J19),NOTOUCH!J18),NOTOUCH!J17),NOTOUCH!J16),NOTOUCH!J15),NOTOUCH!J14),NOTOUCH!J13),NOTOUCH!J12),NOTOUCH!J11),NOTOUCH!J10),NOTOUCH!J9),NOTOUCH!J8),NOTOUCH!J7),NOTOUCH!J6),NOTOUCH!J5),NOTOUCH!J4),NOTOUCH!J3),NOTOUCH!J2)
G3G3=IF(ISERROR(SEARCH(NOTOUCH!I2, B3)),IF(ISERROR(SEARCH(NOTOUCH!I3,B3)),IF(ISERROR(SEARCH(NOTOUCH!I4,B3)),IF(ISERROR(SEARCH(NOTOUCH!I5,B3)),IF(ISERROR(SEARCH(NOTOUCH!I6,B3)),IF(ISERROR(SEARCH(NOTOUCH!I7,B3)),IF(ISERROR(SEARCH(NOTOUCH!I8,B3)),IF(ISERROR(SEARCH(NOTOUCH!I9,B3)),IF(ISERROR(SEARCH(NOTOUCH!I10,B3)),IF(ISERROR(SEARCH(NOTOUCH!I11,B3)),IF(ISERROR(SEARCH(NOTOUCH!I12,B3)),IF(ISERROR(SEARCH(NOTOUCH!I13,B3)),IF(ISERROR(SEARCH(NOTOUCH!I14,B3)),IF(ISERROR(SEARCH(NOTOUCH!I15,B3)),IF(ISERROR(SEARCH(NOTOUCH!I16,B3)),IF(ISERROR(SEARCH(NOTOUCH!I17,B3)),IF(ISERROR(SEARCH(NOTOUCH!I18,B3)),IF(ISERROR(SEARCH(NOTOUCH!I19,B3)),IF(ISERROR(SEARCH(NOTOUCH!I20,B3)),IF(ISERROR(SEARCH(NOTOUCH!I21,B3)),IF(ISERROR(SEARCH(NOTOUCH!I22,B3)),IF(ISERROR(SEARCH(NOTOUCH!I23,B3)),IF(ISERROR(SEARCH(NOTOUCH!I24,B3)),IF(ISERROR(SEARCH(NOTOUCH!I25,B3)),IF(ISERROR(SEARCH(NOTOUCH!I26,B3)),IF(ISERROR(SEARCH(NOTOUCH!I27,B3)),IF(ISERROR(SEARCH(NOTOUCH!I28,B3)),IF(ISERROR(SEARCH(NOTOUCH!I29,B3)),IF(ISERROR(SEARCH(NOTOUCH!I30,B3)),IF(ISERROR(SEARCH(NOTOUCH!I31,B3)),"",NOTOUCH!J31),NOTOUCH!J30),NOTOUCH!J29),NOTOUCH!J28),NOTOUCH!J27),NOTOUCH!J26),NOTOUCH!J25),NOTOUCH!J24),NOTOUCH!J23),NOTOUCH!J22),NOTOUCH!J21),NOTOUCH!J20),NOTOUCH!J19),NOTOUCH!J18),NOTOUCH!J17),NOTOUCH!J16),NOTOUCH!J15),NOTOUCH!J14),NOTOUCH!J13),NOTOUCH!J12),NOTOUCH!J11),NOTOUCH!J10),NOTOUCH!J9),NOTOUCH!J8),NOTOUCH!J7),NOTOUCH!J6),NOTOUCH!J5),NOTOUCH!J4),NOTOUCH!J3),NOTOUCH!J2)
D4D4=SUM(C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12,C13,C14,C15,C16,C17,C18,C19,C20,C21,C22,C23,C24,C25,C26,C27,C28,C29,C30,C31,C32,C33,C34,C35,C36,C37,C38,C39,C40,C41,C42,C43,C44,C45,C46,C47,C48,C49,C50)
F4F4=IF(ISERROR(SEARCH(NOTOUCH!G2,B4)),IF(ISERROR(SEARCH(NOTOUCH!G3,B4)),IF(ISERROR(SEARCH(NOTOUCH!G4,B4)),IF(ISERROR(SEARCH(NOTOUCH!G5,B4)),IF(ISERROR(SEARCH(NOTOUCH!G6,B4)),IF(ISERROR(SEARCH(NOTOUCH!G7,B4)),IF(ISERROR(SEARCH(NOTOUCH!G8,B4)),IF(ISERROR(SEARCH(NOTOUCH!G9,B4)),IF(ISERROR(SEARCH(NOTOUCH!G10,B4)),IF(ISERROR(SEARCH(NOTOUCH!G11,B4)),IF(ISERROR(SEARCH(NOTOUCH!G12,B4)),IF(ISERROR(SEARCH(NOTOUCH!G13,B4)),IF(ISERROR(SEARCH(NOTOUCH!G14,B4)),IF(ISERROR(SEARCH(NOTOUCH!G15,B4)),IF(ISERROR(SEARCH(NOTOUCH!G16,B4)),IF(ISERROR(SEARCH(NOTOUCH!G17,B4)),IF(ISERROR(SEARCH(NOTOUCH!G18,B4)),IF(ISERROR(SEARCH(NOTOUCH!G19,B4)),IF(ISERROR(SEARCH(NOTOUCH!G20,B4)),IF(ISERROR(SEARCH(NOTOUCH!G21,B4)),IF(ISERROR(SEARCH(NOTOUCH!G22,B4)),IF(ISERROR(SEARCH(NOTOUCH!G23,B4)),IF(ISERROR(SEARCH(NOTOUCH!G24,B4)),IF(ISERROR(SEARCH(NOTOUCH!G25,B4)),IF(ISERROR(SEARCH(NOTOUCH!G26,B4)),IF(ISERROR(SEARCH(NOTOUCH!G27,B4)),IF(ISERROR(SEARCH(NOTOUCH!G28,B4)),IF(ISERROR(SEARCH(NOTOUCH!G29,B4)),IF(ISERROR(SEARCH(NOTOUCH!G30,B4)),IF(ISERROR(SEARCH(NOTOUCH!G31,B4)),IF(ISERROR(SEARCH(NOTOUCH!G32,B4)),IF(ISERROR(SEARCH(NOTOUCH!G33,B4)),IF(ISERROR(SEARCH(NOTOUCH!G34,B4)),IF(ISERROR(SEARCH(NOTOUCH!G35,B4)),IF(ISERROR(SEARCH(NOTOUCH!G36,B4)),IF(ISERROR(SEARCH(NOTOUCH!G37,B4)),IF(ISERROR(SEARCH(NOTOUCH!G38,B4)),IF(ISERROR(SEARCH(NOTOUCH!G39,B4)),IF(ISERROR(SEARCH(NOTOUCH!G40,B4)),IF(ISERROR(SEARCH(NOTOUCH!G41,B4)),IF(ISERROR(SEARCH(NOTOUCH!G42,B4)),IF(ISERROR(SEARCH(NOTOUCH!G43,B4)),IF(ISERROR(SEARCH(NOTOUCH!G44,B4)),IF(ISERROR(SEARCH(NOTOUCH!G45,B4)),IF(ISERROR(SEARCH(NOTOUCH!G46,B4)),IF(ISERROR(SEARCH(NOTOUCH!G47,B4)),IF(ISERROR(SEARCH(NOTOUCH!G48,B4)),IF(ISERROR(SEARCH(NOTOUCH!G49,B4)),IF(ISERROR(SEARCH(NOTOUCH!G50,B4)),IF(ISERROR(SEARCH(NOTOUCH!G51,B4)),"",NOTOUCH!H51),NOTOUCH!H50),NOTOUCH!H49),NOTOUCH!H48),NOTOUCH!H47),NOTOUCH!H46),NOTOUCH!H45),NOTOUCH!H44),NOTOUCH!H43),NOTOUCH!H42),NOTOUCH!H41),NOTOUCH!H40),NOTOUCH!H39),NOTOUCH!H38),NOTOUCH!H37),NOTOUCH!H36),NOTOUCH!H35),NOTOUCH!H34),NOTOUCH!H33),NOTOUCH!H32),NOTOUCH!H31),NOTOUCH!H30),NOTOUCH!H29),NOTOUCH!H28),NOTOUCH!H27),NOTOUCH!H26),NOTOUCH!H25),NOTOUCH!H24),NOTOUCH!H23),NOTOUCH!H22),NOTOUCH!H21),NOTOUCH!H20),NOTOUCH!H19),NOTOUCH!H18),NOTOUCH!H17),NOTOUCH!H16),NOTOUCH!H15),NOTOUCH!H14),NOTOUCH!H13),NOTOUCH!H12),NOTOUCH!H11),NOTOUCH!H10),NOTOUCH!H9),NOTOUCH!H8),NOTOUCH!H7),NOTOUCH!H6),NOTOUCH!H5),NOTOUCH!H4),NOTOUCH!H3),NOTOUCH!H2)
F5F5=IF(ISERROR(SEARCH(NOTOUCH!G2,B5)),IF(ISERROR(SEARCH(NOTOUCH!G3,B5)),IF(ISERROR(SEARCH(NOTOUCH!G4,B5)),IF(ISERROR(SEARCH(NOTOUCH!G5,B5)),IF(ISERROR(SEARCH(NOTOUCH!G6,B5)),IF(ISERROR(SEARCH(NOTOUCH!G7,B5)),IF(ISERROR(SEARCH(NOTOUCH!G8,B5)),IF(ISERROR(SEARCH(NOTOUCH!G9,B5)),IF(ISERROR(SEARCH(NOTOUCH!G10,B5)),IF(ISERROR(SEARCH(NOTOUCH!G11,B5)),IF(ISERROR(SEARCH(NOTOUCH!G12,B5)),IF(ISERROR(SEARCH(NOTOUCH!G13,B5)),IF(ISERROR(SEARCH(NOTOUCH!G14,B5)),IF(ISERROR(SEARCH(NOTOUCH!G15,B5)),IF(ISERROR(SEARCH(NOTOUCH!G16,B5)),IF(ISERROR(SEARCH(NOTOUCH!G17,B5)),IF(ISERROR(SEARCH(NOTOUCH!G18,B5)),IF(ISERROR(SEARCH(NOTOUCH!G19,B5)),IF(ISERROR(SEARCH(NOTOUCH!G20,B5)),IF(ISERROR(SEARCH(NOTOUCH!G21,B5)),IF(ISERROR(SEARCH(NOTOUCH!G22,B5)),IF(ISERROR(SEARCH(NOTOUCH!G23,B5)),IF(ISERROR(SEARCH(NOTOUCH!G24,B5)),IF(ISERROR(SEARCH(NOTOUCH!G25,B5)),IF(ISERROR(SEARCH(NOTOUCH!G26,B5)),IF(ISERROR(SEARCH(NOTOUCH!G27,B5)),IF(ISERROR(SEARCH(NOTOUCH!G28,B5)),IF(ISERROR(SEARCH(NOTOUCH!G29,B5)),IF(ISERROR(SEARCH(NOTOUCH!G30,B5)),IF(ISERROR(SEARCH(NOTOUCH!G31,B5)),IF(ISERROR(SEARCH(NOTOUCH!G32,B5)),IF(ISERROR(SEARCH(NOTOUCH!G33,B5)),IF(ISERROR(SEARCH(NOTOUCH!G34,B5)),IF(ISERROR(SEARCH(NOTOUCH!G35,B5)),IF(ISERROR(SEARCH(NOTOUCH!G36,B5)),IF(ISERROR(SEARCH(NOTOUCH!G37,B5)),IF(ISERROR(SEARCH(NOTOUCH!G38,B5)),IF(ISERROR(SEARCH(NOTOUCH!G39,B5)),IF(ISERROR(SEARCH(NOTOUCH!G40,B5)),IF(ISERROR(SEARCH(NOTOUCH!G41,B5)),IF(ISERROR(SEARCH(NOTOUCH!G42,B5)),IF(ISERROR(SEARCH(NOTOUCH!G43,B5)),IF(ISERROR(SEARCH(NOTOUCH!G44,B5)),IF(ISERROR(SEARCH(NOTOUCH!G45,B5)),IF(ISERROR(SEARCH(NOTOUCH!G46,B5)),IF(ISERROR(SEARCH(NOTOUCH!G47,B5)),IF(ISERROR(SEARCH(NOTOUCH!G48,B5)),IF(ISERROR(SEARCH(NOTOUCH!G49,B5)),IF(ISERROR(SEARCH(NOTOUCH!G50,B5)),IF(ISERROR(SEARCH(NOTOUCH!G51,B5)),"",NOTOUCH!H51),NOTOUCH!H50),NOTOUCH!H49),NOTOUCH!H48),NOTOUCH!H47),NOTOUCH!H46),NOTOUCH!H45),NOTOUCH!H44),NOTOUCH!H43),NOTOUCH!H42),NOTOUCH!H41),NOTOUCH!H40),NOTOUCH!H39),NOTOUCH!H38),NOTOUCH!H37),NOTOUCH!H36),NOTOUCH!H35),NOTOUCH!H34),NOTOUCH!H33),NOTOUCH!H32),NOTOUCH!H31),NOTOUCH!H30),NOTOUCH!H29),NOTOUCH!H28),NOTOUCH!H27),NOTOUCH!H26),NOTOUCH!H25),NOTOUCH!H24),NOTOUCH!H23),NOTOUCH!H22),NOTOUCH!H21),NOTOUCH!H20),NOTOUCH!H19),NOTOUCH!H18),NOTOUCH!H17),NOTOUCH!H16),NOTOUCH!H15),NOTOUCH!H14),NOTOUCH!H13),NOTOUCH!H12),NOTOUCH!H11),NOTOUCH!H10),NOTOUCH!H9),NOTOUCH!H8),NOTOUCH!H7),NOTOUCH!H6),NOTOUCH!H5),NOTOUCH!H4),NOTOUCH!H3),NOTOUCH!H2)
G5G5=IF(ISERROR(SEARCH(NOTOUCH!I2, B5)),IF(ISERROR(SEARCH(NOTOUCH!I3,B5)),IF(ISERROR(SEARCH(NOTOUCH!I4,B5)),IF(ISERROR(SEARCH(NOTOUCH!I5,B5)),IF(ISERROR(SEARCH(NOTOUCH!I6,B5)),IF(ISERROR(SEARCH(NOTOUCH!I7,B5)),IF(ISERROR(SEARCH(NOTOUCH!I8,B5)),IF(ISERROR(SEARCH(NOTOUCH!I9,B5)),IF(ISERROR(SEARCH(NOTOUCH!I10,B5)),IF(ISERROR(SEARCH(NOTOUCH!I11,B5)),IF(ISERROR(SEARCH(NOTOUCH!I12,B5)),IF(ISERROR(SEARCH(NOTOUCH!I13,B5)),IF(ISERROR(SEARCH(NOTOUCH!I14,B5)),IF(ISERROR(SEARCH(NOTOUCH!I15,B5)),IF(ISERROR(SEARCH(NOTOUCH!I16,B5)),IF(ISERROR(SEARCH(NOTOUCH!I17,B5)),IF(ISERROR(SEARCH(NOTOUCH!I18,B5)),IF(ISERROR(SEARCH(NOTOUCH!I19,B5)),IF(ISERROR(SEARCH(NOTOUCH!I20,B5)),IF(ISERROR(SEARCH(NOTOUCH!I21,B5)),IF(ISERROR(SEARCH(NOTOUCH!I22,B5)),IF(ISERROR(SEARCH(NOTOUCH!I23,B5)),IF(ISERROR(SEARCH(NOTOUCH!I24,B5)),IF(ISERROR(SEARCH(NOTOUCH!I25,B5)),IF(ISERROR(SEARCH(NOTOUCH!I26,B5)),IF(ISERROR(SEARCH(NOTOUCH!I27,B5)),IF(ISERROR(SEARCH(NOTOUCH!I28,B5)),IF(ISERROR(SEARCH(NOTOUCH!I29,B5)),IF(ISERROR(SEARCH(NOTOUCH!I30,B5)),IF(ISERROR(SEARCH(NOTOUCH!I31,B5)),"",NOTOUCH!J31),NOTOUCH!J30),NOTOUCH!J29),NOTOUCH!J28),NOTOUCH!J27),NOTOUCH!J26),NOTOUCH!J25),NOTOUCH!J24),NOTOUCH!J23),NOTOUCH!J22),NOTOUCH!J21),NOTOUCH!J20),NOTOUCH!J19),NOTOUCH!J18),NOTOUCH!J17),NOTOUCH!J16),NOTOUCH!J15),NOTOUCH!J14),NOTOUCH!J13),NOTOUCH!J12),NOTOUCH!J11),NOTOUCH!J10),NOTOUCH!J9),NOTOUCH!J8),NOTOUCH!J7),NOTOUCH!J6),NOTOUCH!J5),NOTOUCH!J4),NOTOUCH!J3),NOTOUCH!J2)


Above is a sample input sheet, under ideal conditions no further user input would be necessary beyond this information. The task/activity codes have to be manually input, which is the most time-consuming task that the sheet can't do by itself. In order to speed that process up, I have a very crude keyword search set up in those cells, which occasionally will guess the codes correctly and save the user some time. It still needs to be manually reviewed, however.

Book1
ABCDEFGHI
1DateTimekeeperClientMatterTaskActivityBillableHoursWorkNarrative
21/10/2020MWH1181251L110A106Y0.1Attend telephonic Status conference with insurance carrier re: discovery status and settlement issues.
31/10/2020MWH1181671L330A101Y1.2Prepare for deposition of expert Michael Steingart, D.O. (Review reports, medical records from chiropractor, surgeon and imaging reports over 300 pages) .
41/10/2020MWH1181671L330A109Y3.8Travel to/from Phoenix for deposition of Plaintiff expert Michael Steingart, D.O. (Elise Smith advised by letter 12-11-19).
51/10/2020MWH1181671L330A109Y1.6Attend deposition of expert Dr. Steingart in Phoenix.
EXPORT
Cell Formulas
RangeFormula
A2A2=IF(ISBLANK(INPUT!A2),"",INPUT!D2)
B2B2=IF(ISBLANK(INPUT!A2),"",INPUT!E2)
C2:C5C2=IF(ISBLANK(INPUT!A2),"",INPUT!I2)
D2:D5D2=IF(ISBLANK(INPUT!A2),"",INPUT!J2)
E2:F5E2=IF(INPUT!F2=0,"",INPUT!F2)
G2:G5G2=IF(ISBLANK(INPUT!A2),"",INPUT!L2)
H2:H5H2=IF(ISBLANK(INPUT!A2),"",INPUT!C2)
I2I2=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(INPUT!B2,NOTOUCH!E2, NOTOUCH!F2),NOTOUCH!E3,NOTOUCH!F3),NOTOUCH!E4,NOTOUCH!F4),NOTOUCH!E5,NOTOUCH!F5),NOTOUCH!E6,NOTOUCH!F6),NOTOUCH!E7,NOTOUCH!F7),NOTOUCH!E8,NOTOUCH!F8),NOTOUCH!E9,NOTOUCH!F9),NOTOUCH!E10,NOTOUCH!F10),NOTOUCH!E11,NOTOUCH!F11),NOTOUCH!E12,NOTOUCH!F12),NOTOUCH!E13,NOTOUCH!F13),NOTOUCH!E14,NOTOUCH!F14),NOTOUCH!E15,NOTOUCH!F15),NOTOUCH!E16,NOTOUCH!F16),NOTOUCH!E17,NOTOUCH!F17),NOTOUCH!E18,NOTOUCH!F18),NOTOUCH!E19,NOTOUCH!F19),NOTOUCH!E20,NOTOUCH!F20),NOTOUCH!E21,NOTOUCH!F21),NOTOUCH!E22,NOTOUCH!F22),NOTOUCH!E23,NOTOUCH!F23),NOTOUCH!E24,NOTOUCH!F24),NOTOUCH!E25,NOTOUCH!F25),NOTOUCH!E26,NOTOUCH!F26),NOTOUCH!E27,NOTOUCH!F27),NOTOUCH!E28,NOTOUCH!F28),NOTOUCH!E29,NOTOUCH!F29),NOTOUCH!E30,NOTOUCH!F30),NOTOUCH!E31,NOTOUCH!F31),NOTOUCH!E32,NOTOUCH!F32),NOTOUCH!E33,NOTOUCH!F33),NOTOUCH!E34,NOTOUCH!F34),NOTOUCH!E35,NOTOUCH!F35),NOTOUCH!E36,NOTOUCH!F36),NOTOUCH!E37,NOTOUCH!F37),NOTOUCH!E38,NOTOUCH!F38),NOTOUCH!E39,NOTOUCH!F39),NOTOUCH!E40,NOTOUCH!F40),NOTOUCH!E41,NOTOUCH!F41),NOTOUCH!E42,NOTOUCH!F42),NOTOUCH!E43,NOTOUCH!F43),NOTOUCH!E44,NOTOUCH!F44),NOTOUCH!E45,NOTOUCH!F45),NOTOUCH!E46,NOTOUCH!F46),NOTOUCH!E47,NOTOUCH!F47),NOTOUCH!E48,NOTOUCH!F48),NOTOUCH!E49,NOTOUCH!F49),NOTOUCH!E50,NOTOUCH!F50),NOTOUCH!E51,NOTOUCH!F51),NOTOUCH!E52,NOTOUCH!F52),NOTOUCH!E53,NOTOUCH!F53),NOTOUCH!E54,NOTOUCH!F54),NOTOUCH!E55,NOTOUCH!F55),NOTOUCH!E56,NOTOUCH!F56),NOTOUCH!E57,NOTOUCH!F57),NOTOUCH!E58,NOTOUCH!F58),NOTOUCH!E59,NOTOUCH!F59),NOTOUCH!E60,NOTOUCH!F60),NOTOUCH!E61,NOTOUCH!F61),NOTOUCH!E62,NOTOUCH!F62),NOTOUCH!E63,NOTOUCH!F63),NOTOUCH!E64,NOTOUCH!F64),NOTOUCH!E65,NOTOUCH!F65),NOTOUCH!E66,NOTOUCH!F66)&IF(ISBLANK(INPUT!B2),"",".")
A3A3=IF(ISBLANK(INPUT!A3),"",INPUT!D2)
B3B3=IF(ISBLANK(INPUT!A3),"",INPUT!E2)
I3I3=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(INPUT!B3,NOTOUCH!E2, NOTOUCH!F2),NOTOUCH!E3,NOTOUCH!F3),NOTOUCH!E4,NOTOUCH!F4),NOTOUCH!E5,NOTOUCH!F5),NOTOUCH!E6,NOTOUCH!F6),NOTOUCH!E7,NOTOUCH!F7),NOTOUCH!E8,NOTOUCH!F8),NOTOUCH!E9,NOTOUCH!F9),NOTOUCH!E10,NOTOUCH!F10),NOTOUCH!E11,NOTOUCH!F11),NOTOUCH!E12,NOTOUCH!F12),NOTOUCH!E13,NOTOUCH!F13),NOTOUCH!E14,NOTOUCH!F14),NOTOUCH!E15,NOTOUCH!F15),NOTOUCH!E16,NOTOUCH!F16),NOTOUCH!E17,NOTOUCH!F17),NOTOUCH!E18,NOTOUCH!F18),NOTOUCH!E19,NOTOUCH!F19),NOTOUCH!E20,NOTOUCH!F20),NOTOUCH!E21,NOTOUCH!F21),NOTOUCH!E22,NOTOUCH!F22),NOTOUCH!E23,NOTOUCH!F23),NOTOUCH!E24,NOTOUCH!F24),NOTOUCH!E25,NOTOUCH!F25),NOTOUCH!E26,NOTOUCH!F26),NOTOUCH!E27,NOTOUCH!F27),NOTOUCH!E28,NOTOUCH!F28),NOTOUCH!E29,NOTOUCH!F29),NOTOUCH!E30,NOTOUCH!F30),NOTOUCH!E31,NOTOUCH!F31),NOTOUCH!E32,NOTOUCH!F32),NOTOUCH!E33,NOTOUCH!F33),NOTOUCH!E34,NOTOUCH!F34),NOTOUCH!E35,NOTOUCH!F35),NOTOUCH!E36,NOTOUCH!F36),NOTOUCH!E37,NOTOUCH!F37),NOTOUCH!E38,NOTOUCH!F38),NOTOUCH!E39,NOTOUCH!F39),NOTOUCH!E40,NOTOUCH!F40),NOTOUCH!E41,NOTOUCH!F41),NOTOUCH!E42,NOTOUCH!F42),NOTOUCH!E43,NOTOUCH!F43),NOTOUCH!E44,NOTOUCH!F44),NOTOUCH!E45,NOTOUCH!F45),NOTOUCH!E46,NOTOUCH!F46),NOTOUCH!E47,NOTOUCH!F47),NOTOUCH!E48,NOTOUCH!F48),NOTOUCH!E49,NOTOUCH!F49),NOTOUCH!E50,NOTOUCH!F50),NOTOUCH!E51,NOTOUCH!F51),NOTOUCH!E52,NOTOUCH!F52),NOTOUCH!E53,NOTOUCH!F53),NOTOUCH!E54,NOTOUCH!F54),NOTOUCH!E55,NOTOUCH!F55),NOTOUCH!E56,NOTOUCH!F56),NOTOUCH!E57,NOTOUCH!F57),NOTOUCH!E58,NOTOUCH!F58),NOTOUCH!E59,NOTOUCH!F59),NOTOUCH!E60,NOTOUCH!F60),NOTOUCH!E61,NOTOUCH!F61),NOTOUCH!E62,NOTOUCH!F62),NOTOUCH!E63,NOTOUCH!F63),NOTOUCH!E64,NOTOUCH!F64),NOTOUCH!E65,NOTOUCH!F65),NOTOUCH!E66,NOTOUCH!F66)&IF(ISBLANK(INPUT!B3),"",".")
A4A4=IF(ISBLANK(INPUT!A4),"",INPUT!D2)
B4B4=IF(ISBLANK(INPUT!A4),"",INPUT!E2)
I4I4=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(INPUT!B4,NOTOUCH!E2, NOTOUCH!F2),NOTOUCH!E3,NOTOUCH!F3),NOTOUCH!E4,NOTOUCH!F4),NOTOUCH!E5,NOTOUCH!F5),NOTOUCH!E6,NOTOUCH!F6),NOTOUCH!E7,NOTOUCH!F7),NOTOUCH!E8,NOTOUCH!F8),NOTOUCH!E9,NOTOUCH!F9),NOTOUCH!E10,NOTOUCH!F10),NOTOUCH!E11,NOTOUCH!F11),NOTOUCH!E12,NOTOUCH!F12),NOTOUCH!E13,NOTOUCH!F13),NOTOUCH!E14,NOTOUCH!F14),NOTOUCH!E15,NOTOUCH!F15),NOTOUCH!E16,NOTOUCH!F16),NOTOUCH!E17,NOTOUCH!F17),NOTOUCH!E18,NOTOUCH!F18),NOTOUCH!E19,NOTOUCH!F19),NOTOUCH!E20,NOTOUCH!F20),NOTOUCH!E21,NOTOUCH!F21),NOTOUCH!E22,NOTOUCH!F22),NOTOUCH!E23,NOTOUCH!F23),NOTOUCH!E24,NOTOUCH!F24),NOTOUCH!E25,NOTOUCH!F25),NOTOUCH!E26,NOTOUCH!F26),NOTOUCH!E27,NOTOUCH!F27),NOTOUCH!E28,NOTOUCH!F28),NOTOUCH!E29,NOTOUCH!F29),NOTOUCH!E30,NOTOUCH!F30),NOTOUCH!E31,NOTOUCH!F31),NOTOUCH!E32,NOTOUCH!F32),NOTOUCH!E33,NOTOUCH!F33),NOTOUCH!E34,NOTOUCH!F34),NOTOUCH!E35,NOTOUCH!F35),NOTOUCH!E36,NOTOUCH!F36),NOTOUCH!E37,NOTOUCH!F37),NOTOUCH!E38,NOTOUCH!F38),NOTOUCH!E39,NOTOUCH!F39),NOTOUCH!E40,NOTOUCH!F40),NOTOUCH!E41,NOTOUCH!F41),NOTOUCH!E42,NOTOUCH!F42),NOTOUCH!E43,NOTOUCH!F43),NOTOUCH!E44,NOTOUCH!F44),NOTOUCH!E45,NOTOUCH!F45),NOTOUCH!E46,NOTOUCH!F46),NOTOUCH!E47,NOTOUCH!F47),NOTOUCH!E48,NOTOUCH!F48),NOTOUCH!E49,NOTOUCH!F49),NOTOUCH!E50,NOTOUCH!F50),NOTOUCH!E51,NOTOUCH!F51),NOTOUCH!E52,NOTOUCH!F52),NOTOUCH!E53,NOTOUCH!F53),NOTOUCH!E54,NOTOUCH!F54),NOTOUCH!E55,NOTOUCH!F55),NOTOUCH!E56,NOTOUCH!F56),NOTOUCH!E57,NOTOUCH!F57),NOTOUCH!E58,NOTOUCH!F58),NOTOUCH!E59,NOTOUCH!F59),NOTOUCH!E60,NOTOUCH!F60),NOTOUCH!E61,NOTOUCH!F61),NOTOUCH!E62,NOTOUCH!F62),NOTOUCH!E63,NOTOUCH!F63),NOTOUCH!E64,NOTOUCH!F64),NOTOUCH!E65,NOTOUCH!F65),NOTOUCH!E66,NOTOUCH!F66)&IF(ISBLANK(INPUT!B4),"",".")
A5A5=IF(ISBLANK(INPUT!A5),"",INPUT!D2)
B5B5=IF(ISBLANK(INPUT!A5),"",INPUT!E2)
I5I5=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(INPUT!B5,NOTOUCH!E2, NOTOUCH!F2),NOTOUCH!E3,NOTOUCH!F3),NOTOUCH!E4,NOTOUCH!F4),NOTOUCH!E5,NOTOUCH!F5),NOTOUCH!E6,NOTOUCH!F6),NOTOUCH!E7,NOTOUCH!F7),NOTOUCH!E8,NOTOUCH!F8),NOTOUCH!E9,NOTOUCH!F9),NOTOUCH!E10,NOTOUCH!F10),NOTOUCH!E11,NOTOUCH!F11),NOTOUCH!E12,NOTOUCH!F12),NOTOUCH!E13,NOTOUCH!F13),NOTOUCH!E14,NOTOUCH!F14),NOTOUCH!E15,NOTOUCH!F15),NOTOUCH!E16,NOTOUCH!F16),NOTOUCH!E17,NOTOUCH!F17),NOTOUCH!E18,NOTOUCH!F18),NOTOUCH!E19,NOTOUCH!F19),NOTOUCH!E20,NOTOUCH!F20),NOTOUCH!E21,NOTOUCH!F21),NOTOUCH!E22,NOTOUCH!F22),NOTOUCH!E23,NOTOUCH!F23),NOTOUCH!E24,NOTOUCH!F24),NOTOUCH!E25,NOTOUCH!F25),NOTOUCH!E26,NOTOUCH!F26),NOTOUCH!E27,NOTOUCH!F27),NOTOUCH!E28,NOTOUCH!F28),NOTOUCH!E29,NOTOUCH!F29),NOTOUCH!E30,NOTOUCH!F30),NOTOUCH!E31,NOTOUCH!F31),NOTOUCH!E32,NOTOUCH!F32),NOTOUCH!E33,NOTOUCH!F33),NOTOUCH!E34,NOTOUCH!F34),NOTOUCH!E35,NOTOUCH!F35),NOTOUCH!E36,NOTOUCH!F36),NOTOUCH!E37,NOTOUCH!F37),NOTOUCH!E38,NOTOUCH!F38),NOTOUCH!E39,NOTOUCH!F39),NOTOUCH!E40,NOTOUCH!F40),NOTOUCH!E41,NOTOUCH!F41),NOTOUCH!E42,NOTOUCH!F42),NOTOUCH!E43,NOTOUCH!F43),NOTOUCH!E44,NOTOUCH!F44),NOTOUCH!E45,NOTOUCH!F45),NOTOUCH!E46,NOTOUCH!F46),NOTOUCH!E47,NOTOUCH!F47),NOTOUCH!E48,NOTOUCH!F48),NOTOUCH!E49,NOTOUCH!F49),NOTOUCH!E50,NOTOUCH!F50),NOTOUCH!E51,NOTOUCH!F51),NOTOUCH!E52,NOTOUCH!F52),NOTOUCH!E53,NOTOUCH!F53),NOTOUCH!E54,NOTOUCH!F54),NOTOUCH!E55,NOTOUCH!F55),NOTOUCH!E56,NOTOUCH!F56),NOTOUCH!E57,NOTOUCH!F57),NOTOUCH!E58,NOTOUCH!F58),NOTOUCH!E59,NOTOUCH!F59),NOTOUCH!E60,NOTOUCH!F60),NOTOUCH!E61,NOTOUCH!F61),NOTOUCH!E62,NOTOUCH!F62),NOTOUCH!E63,NOTOUCH!F63),NOTOUCH!E64,NOTOUCH!F64),NOTOUCH!E65,NOTOUCH!F65),NOTOUCH!E66,NOTOUCH!F66)&IF(ISBLANK(INPUT!B5),"",".")


Here is an example of the corresponding export sheet, where the data is checked for abbreviations and put into the correct format to be accepted by billing software when exported as a .csv file. Regarding your concerns that the abbreviations won't work as intended, it's difficult to tell but some of those entries have spaces strategically placed on either side. Using your example, "regarding" does not translate to "re:garding" because the substitute function is actually looking to replace " re " with " re: ". This works because "re" is never used at the beginning or end of a sentence. For phrases that need proper capitalization and can come at the beginning of a sentence, " CT" is replaced with " correspondence to" while "CT " is then replaced with "Correspondence to ". The replacements have, in my experience, worked out quite nicely, but yes, they should still be reviewed by the user before submission to ensure no typos are incorrect substitutions.

Lastly, I currently have a list of 63 abbreviations, and there are a chain of 64 substitute functions, so I can only fit one more before the renovation becomes mandatory to keep using the system normally.
 
Upvote 0
Editor's note: Looks like the cell fill colors got tossed up in the add-in's process. Oh well, hopefully it's not totally illegible
 
Upvote 0
Blacksmith said:
For phrases that need proper capitalization and can come at the beginning of a sentence, " CT" is replaced with " correspondence to" while "CT " is then replaced with "Correspondence to ".
Click to expand...
Does that mean a data cell will never have more than one sentence? Otherwise ..
Prepare report. CT supervisor.
would become
Prepare report. correspondence to supervisor.
after your first substitution.


Can "re " ever be the first text in a cell/sentence? If so, in that circumstance, should it become "Re: " or "re: "?
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Tally that shifts cells daily.
Replies
3
Views
826
portews
P
D
  • Question Question
Completely cleaning account numbers and storing with a specific format
Replies
2
Views
1K
diversification
D
N
  • Question Question
2-step Dynamic Ranges and Charts
Replies
0
Views
1K
Nick Loughton
N
P
  • Question Question
VBA to perform Text Diff (but with >2500 Char per cell)
Replies
0
Views
294
potatoooooooo
P
D
  • Question Question
"Disabling" calculation of certain formulas until source cells updated -- is there a better way to do this?
Replies
0
Views
721
d0rian
D

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top