Forum Discussion
Helper IIHow to copy a specific string from a column to another
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..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 ) )
Super UserAh, 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.. |
KevinMorneaultHi
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
- 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: