Filter or Remove Unwanted Letters or Characters Leaving Data in Row

[sslattery17]

My input source has a column with primary name and/or residents.  The client sometimes uses designations (H) or (H) Shannon**Slattery** to identify the tenants unit type etc...I have tried to filter this out a few different ways including using regex_replace([Residents],"[^\sa-zA-Z]",'') or regex_replace ([Residents],"(H)",'')  I need the tenant name to stay but any and all designations to be removed.  I even tried the find replace and that did not work as expected.  I have attached screenshots.  With 6 weeks into the use of this tool; any guidance suggestions, would greatly be appreciated.  

  

[sslattery17]

Would this work flow be in addition to different schema macro? Can it be used in conjunction with the different sheet different schema macro.  I have 71 sheets for this workflow.  

[sslattery17]

I updated directory to location of all (76) sheets getting parse error.  see attached.

[binuacs]

@sslattery17 are you expecting the 71 sheets data to be combined into one output? Are these 71 sheets are from one workbook or different excel workbooks? also are these sheets are having different schema?

[sslattery17]

Hello @binuacs.  No on combining output, I load each property separate to audit in our system.  They are separate sheets some have different schemas.  Every client either sends different sheets though should be the same schema, are not; or they send one sheet again always have one or two different schemas.  This client is different sheets & a few different schemas.  I included the workflow my zip...all clients we are capturing & calculating the same sort of data.

[binuacs]

your workflow is not properly uploaded, the macros are missing, you need to go to the menu options-exportworkflow, it will create a zip file which will include all the associated files with this workflow

I am not exactly following your requirement. I assume you will have separate excel file from different clients, my question is do you want to process each files seperately and save as different output based on the client? 

[sslattery17]

@binuacs Thank you.  Find attached. 

[binuacs]

@sslattery17 I am not exactly following your requirement. I assume you will have separate excel file from different clients, my question is do you want to process each files seperately and save as different output based on the client? 

[binuacs]

@sslattery17 can you also attach your macros, they are still missing

[sslattery17]

Please find attached the macros.  Sorry about that; I guess I was not clear on your ask.  I want a different output per sheet (property).  My workflow is same client; each sheet is a different property for that one client.

[sslattery17]

@binuacs @ChrisTX   I am getting parse errors on creating formula for column Rent.  Not sure why I am getting these errors. Can you assist or point me in the right direction to fix.??  The zip with the sample reports outcomes workflow I sent.  a clients charge codes can be anything as you can see below; this prevents me from using just a ifcontains[charge code],"Rent" or "rent" as this picks up code that contains Rent or rent like Garage Rent or Parking rent....etc open to suggestions as long as it does not require a manual reference list.

if [Charge Code]="Rent" then [Amount] elseif [Charge Code]="rent" then [Amount] elseif [Charge Code]="Month to Month Fee" then [Amount] elseif [Charge Code]="Rent 3%" then [Amount] elseif [Charge Code]="Residential Rent" then [Amount] elseif [Charge Code]="Rent-" then [Amount] elseif [Charge Code]="Rent-PEP applied" then [Amount] elseif [Charge Code]="Rent PEP Applied" then [Amount] elseif [Charge Code]="Rent-minus PEP discount" then [Amount] elseif [Charge Code]="Corrected to best price quoted. Lease dates coincide with rate and LRO pricing" then [Amount] elseif [Charge Code]="Rent-Applied PEP" then [Amount] elseif [Charge Code]="Rent-3% PEP Applied" then [Amount] elseif [Charge Code]="Rent edited-incorrect amount" then [Amount] else 0 endif