Forum Discussion
Solved
Search for text among numeric values
Hello community. I need to solve one task. In collum have data that starts with numbers, then we have text, then again numbers, the goal is to select the text between two numbers and to insert it...
Hi DimaMD
As promissed, here is the solution (No need for the letters table)Text = VAR String = 'String Table'[String] VAR StringLength = LEN ( String ) VAR T1 = SELECTCOLUMNS ( GENERATESERIES ( 0, 9, 1 ), "@Number", [Value] & "" ) VAR T2 = SELECTCOLUMNS ( GENERATESERIES ( 1, StringLength ), "@Index", [Value] ) VAR T3 = ADDCOLUMNS ( T2, "@StringLetters", MID ( String, [@Index], 1 ) ) VAR T4 = ADDCOLUMNS ( T3, "@TexLetters", IF ( NOT ( [@StringLetters] IN T1 ), LOWER ( [@StringLetters] ), "|" ) ) VAR T5 = ADDCOLUMNS ( T4, "Text Letters", VAR PreviousIndex = [@Index] - 1 RETURN IF ( MAXX ( FILTER ( T4, [@Index] = PreviousIndex ), [@TexLetters] ) <> "|", [@TexLetters] ) ) VAR T6 = FILTER ( T5, [Text Letters] <> BLANK ( ) ) RETURN PATHITEM ( CONCATENATEX ( T6, [Text Letters] ), 2 )
It's too late now, I'll work on tomorrow morning. However, I have some questions?
do the numbers come with spaces in between as shown in screenshot?
would you like the output to be of decimal/integer data type (can be used later to perform further calculations) or would you like to have them as is (string type)?
No problem, when you can, you are right preferably to have them decimal/integer and yes the numbers are separated by spaces as shown in the screenshot
thank youuu tamer
Amal
Amy_Qc
Hi Amal,Please refer to attached sample file with the solution. Please let me know if you face any trouble implementing the same on your actual data.
Output = VAR String = 'Table'[Input] VAR Items = SUBSTITUTE ( String, " ", "|" ) VAR Length = PATHLENGTH ( Items ) VAR T1 = GENERATESERIES ( 0, 9, 1 ) VAR T2 = GENERATESERIES ( 1, Length, 1 ) VAR T3 = ADDCOLUMNS ( T2, "@Item", PATHITEM ( Items, [Value] ) ) VAR T4 = FILTER ( T3, VAR T5 = FILTER ( T1, CONTAINSSTRING ( [@Item], [Value] ) ) VAR Condition1 = NOT ISEMPTY ( T5 ) VAR Condition2 = NOT ( CONTAINSSTRING ( [@Item], "-" ) ) VAR Condition3 = NOT ( CONTAINSSTRING ( [@Item], "/" ) ) RETURN Condition1 && Condition2 && Condition3 ) VAR Digits = CONCATENATEX ( T4, [@Item] ) VAR Factor = SWITCH ( TRUE ( ), CONTAINSSTRING ( String, "K$" ), 1000, CONTAINSSTRING ( String, "M$" ), 1000000, CONTAINSSTRING ( String, "B$" ), 1000000000 ) RETURN VALUE ( Digits ) * Factor