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 )
Hi tamerj1 in collum1 we have original data. The goal is to make "result" collum, which will copy text, that is located betweeen numbers in collum 1.
| Collum1 | result |
| 000235123 Example text between numbers 234234424 | Example text between numbers |
Hi DimaMD
First step is to create a seperate table containing all the letters that you consider as stringText Letters = SELECTCOLUMNS ( { "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z", " ", "-", "_", ")", "(", "[", "]", ",", ";", ":", "{", "}", "*", "&", "%", "$", "#", "@", "!", "?", "<", ">", "+", "=", "." }, "Letter", [Value] )Then create new column
Text = VAR ValueLength = LEN ( 'Table'[String] ) VAR T3 = GENERATESERIES ( 1, ValueLength ) VAR T4 = ADDCOLUMNS ( T3, "@StringLetter", MID ( 'Table'[String], [Value], 1 ) ) VAR T5 = ADDCOLUMNS ( T4, "@TexLetters", IF ( [@StringLetter] IN VALUES ( 'Text Letters'[Letter] ), LOWER ( [@StringLetter] ) ) ) RETURN CONCATENATEX ( T5, [@TexLetters] )tamerj1 Hello and thank you, great idea I must say.
But the problem is that sometimes te cell has also text after digits, and we need to consider only part of text that is between digits
For example:
Source Result 34234 Test text 3434 another text Test text If we will use your solution it will take all the text, but we need only the part that is between digits.