Forum Discussion
How to copy a specific string from a column to another
- 4 years ago
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.. - 4 years ago
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 ) )
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 ) )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 !
- KevinMorneault4 years ago
Helper II
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