How to copy a specific string from a column to another

[KevinMorneault]

Hi All

I would like to know if this can be done.  I have a column which has text (Summary). I would like to know if i can add a column(ExpenseCode) and copy only some part of the needed from(Summary) text into that new colum(ExpenseCode) Example

Column 1  (Summary)                                                   

Joh Doe

IT Equipment Request

Expense Code : 12344556654

Device: Laptop

Model: Latitude

Cost: 1K

this system needs to be install with windows 11 and other application

please verify with the user ...... etc..

New Colum (Expense Code)

122344556 

Reason i am trying to do this is mainly because i have another table(asset) which has all the data once device as been deployed. I want to be able to validate the information from the asset table with this table. With this scenario i would be able to validate the expense code.

I know i would probably be able to do a case  when clause but was wandering if i can put it a bit more cleaner as the specific column summary has alot of data in one cell.

[jdbuchanan71 · Accepted solution]

Ah, interesting.  Yes, that does change things a bit.  I am going to assume you want the three values in three different columns: Expense Code, GL Code, Invoice Number.  
We add three calculated columns, each set up to look for a different string and pull back all the text after that string up to the next carrige return (UNICHAR(10)) like so:

Expense Code = 
VAR _Code = "Expense Code : "
VAR _CodeLen = LEN ( _Code )
VAR _CodeMissing = ISERROR ( FIND ( _Code, 'DataTable'[Column] ) )
VAR _CodePosition = IF ( _CodeMissing, BLANK (), FIND ( _Code, 'DataTable'[Column] ) + _CodeLen )
VAR _CodeEnd = IF ( _CodeMissing, BLANK (), FIND ( UNICHAR ( 10 ), 'DataTable'[Column], _CodePosition ) )
RETURN
    IF ( _CodeMissing, BLANK (), MID ( 'DataTable'[Column], _CodePosition, _CodeEnd - _CodePosition ) )

GL Code = 
VAR _Code = "GL Code : "
VAR _CodeLen = LEN ( _Code )
VAR _CodeMissing = ISERROR ( FIND ( _Code, 'DataTable'[Column] ) )
VAR _CodePosition = IF ( _CodeMissing, BLANK (), FIND ( _Code, 'DataTable'[Column] ) + _CodeLen )
VAR _CodeEnd = IF ( _CodeMissing, BLANK (), FIND ( UNICHAR ( 10 ), 'DataTable'[Column], _CodePosition ) )
RETURN
    IF ( _CodeMissing, BLANK (), MID ( 'DataTable'[Column], _CodePosition, _CodeEnd - _CodePosition ) )

Invoice Number = 
VAR _Code = "Invoice Number : "
VAR _CodeLen = LEN ( _Code )
VAR _CodeMissing = ISERROR ( FIND ( _Code, 'DataTable'[Column] ) )
VAR _CodePosition = IF ( _CodeMissing, BLANK (), FIND ( _Code, 'DataTable'[Column] ) + _CodeLen )
VAR _CodeEnd = IF ( _CodeMissing, BLANK (), FIND ( UNICHAR ( 10 ), 'DataTable'[Column], _CodePosition ) )
RETURN
    IF ( _CodeMissing, BLANK (), MID ( 'DataTable'[Column], _CodePosition, _CodeEnd - _CodePosition ) )

My sample data has 3 rows that look like this.  You can see that the first entry only had the Expense Code so that is the only one that comes back.

Joh Doe IT Equipment Request Expense Code : 1234 Device: Laptop Model: Latitude Cost: 1K this system needs to be install with windows 11 and other application please verify with the user ...... etc..

Joh Doe IT Equipment Request Expense Code : 6654654654654 GL Code : 987654 Invoice Number : 789321654798 Device: Laptop Model: Latitude Cost: 1K this system needs to be install with windows 11 and other application please verify with the user ...... etc..

Joh Doe IT Equipment Request Expense Code : 1455654 GL Code : 987654 Invoice Number : Device: Laptop Model: Latitude Cost: 1K this system needs to be install with windows 11 and other application please verify with the user ...... etc..

[jdbuchanan71]

Change the string it is searching for.  Instead of searching for "Expense Code : " search for "ExpenseCode: ExpenseCode "

Yes, even include the space at the end so the formula can find the exact position where the actual code starts.

Expense Code = 
VAR _Code = "ExpenseCode:  ExpenseCode "
VAR _CodeLen = LEN ( _Code )
VAR _CodeMissing = ISERROR ( FIND ( _Code, 'DataTable'[Column] ) )
VAR _CodePosition = IF ( _CodeMissing, BLANK (), FIND ( _Code, 'DataTable'[Column] ) + _CodeLen )
VAR _CodeEnd = IF ( _CodeMissing, BLANK (), FIND ( UNICHAR ( 10 ), 'DataTable'[Column], _CodePosition ) )
RETURN
    IF ( _CodeMissing, BLANK (), MID ( 'DataTable'[Column], _CodePosition, _CodeEnd - _CodePosition ) )

The rest of the code will take care of removing the extra characters.  That is what VAR _CodePosition and VAR _CodeEnd do.

[jdbuchanan71 · Accepted solution]

It looks like you need to adjust the _Code VAR to match your real data.

Expense Code = 
VAR _Code = "Expense Code : "

This first VAR is set to the string "Expense Code : ",  this is the string it is searching for in the data but in your data it looks like the string is "ExpenseCode: " so you would have to change the measure to match.

Expense Code = 
VAR _Code = "ExpenseCode: "
VAR _CodeLen = LEN ( _Code )
VAR _CodeMissing = ISERROR ( FIND ( _Code, 'DataTable'[Column] ) )
VAR _CodePosition = IF ( _CodeMissing, BLANK (), FIND ( _Code, 'DataTable'[Column] ) + _CodeLen )
VAR _CodeEnd = IF ( _CodeMissing, BLANK (), FIND ( UNICHAR ( 10 ), 'DataTable'[Column], _CodePosition ) )
RETURN
    IF ( _CodeMissing, BLANK (), MID ( 'DataTable'[Column], _CodePosition, _CodeEnd - _CodePosition ) )

[KevinMorneault]

Tks it does work what i did wrong is the following

ExpenseCode, GL or Invoice: ExpenseCode Coding (Division 1)  ExpenseCode:  ExpenseCode

For division one  where it says Var _Code = ExpenseCode, GL or Invoice:"

by only copying this part it works, the question i would have is now i do have the code but i have the remaining in front of the code 

ExpenseCode Coding (Division 1)  ExpenseCode:  ExpenseCode 111111222333322211

Is there a way to only have the number

I did the same for GL Code, this one works as well and only show numbers. 

Tks again really appreciated and great work ! 

[KevinMorneault]

Hi

I was able to remove the beginning of the expense code and only show the number by using Right, only issue is if i dont have a value for the expense code  it will show the last 30 characters  instead of the number mainly because it would be a GL code it would show. is there a way in case it does not have a expense code to leave it blank

tks

[jdbuchanan71 · Accepted solution]

Ah, interesting.  Yes, that does change things a bit.  I am going to assume you want the three values in three different columns: Expense Code, GL Code, Invoice Number.  
We add three calculated columns, each set up to look for a different string and pull back all the text after that string up to the next carrige return (UNICHAR(10)) like so:

Expense Code = 
VAR _Code = "Expense Code : "
VAR _CodeLen = LEN ( _Code )
VAR _CodeMissing = ISERROR ( FIND ( _Code, 'DataTable'[Column] ) )
VAR _CodePosition = IF ( _CodeMissing, BLANK (), FIND ( _Code, 'DataTable'[Column] ) + _CodeLen )
VAR _CodeEnd = IF ( _CodeMissing, BLANK (), FIND ( UNICHAR ( 10 ), 'DataTable'[Column], _CodePosition ) )
RETURN
    IF ( _CodeMissing, BLANK (), MID ( 'DataTable'[Column], _CodePosition, _CodeEnd - _CodePosition ) )

GL Code = 
VAR _Code = "GL Code : "
VAR _CodeLen = LEN ( _Code )
VAR _CodeMissing = ISERROR ( FIND ( _Code, 'DataTable'[Column] ) )
VAR _CodePosition = IF ( _CodeMissing, BLANK (), FIND ( _Code, 'DataTable'[Column] ) + _CodeLen )
VAR _CodeEnd = IF ( _CodeMissing, BLANK (), FIND ( UNICHAR ( 10 ), 'DataTable'[Column], _CodePosition ) )
RETURN
    IF ( _CodeMissing, BLANK (), MID ( 'DataTable'[Column], _CodePosition, _CodeEnd - _CodePosition ) )

Invoice Number = 
VAR _Code = "Invoice Number : "
VAR _CodeLen = LEN ( _Code )
VAR _CodeMissing = ISERROR ( FIND ( _Code, 'DataTable'[Column] ) )
VAR _CodePosition = IF ( _CodeMissing, BLANK (), FIND ( _Code, 'DataTable'[Column] ) + _CodeLen )
VAR _CodeEnd = IF ( _CodeMissing, BLANK (), FIND ( UNICHAR ( 10 ), 'DataTable'[Column], _CodePosition ) )
RETURN
    IF ( _CodeMissing, BLANK (), MID ( 'DataTable'[Column], _CodePosition, _CodeEnd - _CodePosition ) )

My sample data has 3 rows that look like this.  You can see that the first entry only had the Expense Code so that is the only one that comes back.

Joh Doe IT Equipment Request Expense Code : 1234 Device: Laptop Model: Latitude Cost: 1K this system needs to be install with windows 11 and other application please verify with the user ...... etc..

Joh Doe IT Equipment Request Expense Code : 6654654654654 GL Code : 987654 Invoice Number : 789321654798 Device: Laptop Model: Latitude Cost: 1K this system needs to be install with windows 11 and other application please verify with the user ...... etc..

Joh Doe IT Equipment Request Expense Code : 1455654 GL Code : 987654 Invoice Number : Device: Laptop Model: Latitude Cost: 1K this system needs to be install with windows 11 and other application please verify with the user ...... etc..

[KevinMorneault]

Hi how r u i try but this time the colum is blank no data i will check again and review. Here's the full detail which is fill out i remove all sensitive information and this is exactly how it is displayed in the table so basically each work order will have all of this information provided in 1 cell in the colum name DetailDescription. One thing is for each individual WO, its either  the expensecode or GL Code  as you will see in the approval information.

Tks

here's the information

--- Approval Information ---

Department:

Organization:

ExpenseCode, GL or Invoice: ExpenseCode Coding (Division 1)  ExpenseCode:  ExpenseCode

  GL Code:

COA Approver:

--- Requester Information ---

Request Contact:

Dept:

Branch:

Phone:

Email:

--- Request For Information ---

Active Account: Yes: They have an existing and active account

 New Employee Name:   

  Existing Employee Name:

Email Address:

Dept/:

Branch:

Building:

Office Location:

Street Address:

City:

Province:

Postal Code:

Phone:

--- Computer Information ---

Device Type Requested:

Requested Desktop:

Quantity:

Requested Laptop:

Quantity:

Requested Other Device Requirement/Rationale:

--- Monitor Information ---

Monitor Requested:

Quantity:

Other Monitor Requirement/Rationale:

--- Software Information ---

Asset Tag:

Software Name(s): ["~Other"]

Note:  If eSignature is requested, it will be provisioned on

Software Quantity comments: 1 of each software title requested

Other Software Information:

--- Peripheral Information ---

Peripheral(s) Requested:

  Computers and Accessories Requested:

  Data Storage Requested:

  Headsets and Earbuds Requested:

Keyboards and Mice Requested:

Speakers Requested:

Webcam Requested:

Other Peripheral Information:

Quantity:

  Quantity more than 1 Details:

Requested Completion Date: 07/18/2022

--- Printer Information ---

Onsite Contact Name:

Onsite Contact Email:

Onsite Contact Phone:

Special Location Notes:

Number of employees to use device:

Elevator:

New/Replacement:

  Replacement Make, Model & Serial/Asset: , ,

Requested Printer:

Printer Options:

Printer Options Details:

Local Connection:

Unique Software:

Business Requirement/Rationale:

Additional Information:

[KevinMorneault]

Hi 

Tks it is reallly appreciated. I will give it a try and update. Basically this is within Remedy via the DWP module. When a request for NewIT Equipment is send, they need to fill out a form which contains section like the following.

Approval Information

Request Information

Reques for information

Computer information

Monitor information

Software information

Peripheral information

Printer Information

Business Requirement/Rational

All of this have approx a total of 60 line all dump in one cell for each WO created.  At first we had requested for the to create a table to make it easier for us to handle but thats not what they did, its a dump in the ticket. Now What i am trying to do is basically extract the information needed to validate against our asset management. 

IF what you provided me work, i will adjust and export/add more column for the device information and other information. I try to think how can this be done differently but so far its the only way i can think of.

I will try what you provided and update you.

Tks again 

[jdbuchanan71]

@KevinMorneault 

You can add the column like this.

Expense Code =
IF (
    CONTAINSSTRING ( YourTable[Column 1  (Summary)], "Expense Code" ),
    SUBSTITUTE ( YourTable[Column 1  (Summary)], "Expense Code : ", "" )
)

[KevinMorneault]

Hi 

Tks i have tried it but it copy the everything from the column. I know why so here's what happen

the Line Expense Code:  has more info within the same line so heres an example of the line, cannot really copy and past the whole thing due to sensitive information..

--- Approval Information ---
Department: Department Name
Organization: Org Name
ExpenseCode, GL or Invoice: Expense Code (Division1) Expense Code: 12345678.98877.776655.444
GL Code:
COA Approver: approver email

So some scenario it would be Expense code and other would be GL as we have 2 Divsion.

right now it just copy everything to the new column. 

What you provided is exactly what i am looking for.

[KevinMorneault]

One thing i forgot to mention is this data is all within one field. so basically it's a form they fill out and the data is being dump in 1 field. within the specific colum.