Extract number from text string based on conditions

diedrich08

Here's one method:

1. Create a function using the following code. This function takes a text input and returns the first 7-digit number found (if any).
2. Invoke this function to create a custom column

(InputText as text) =>
let
  RequiredLength = 7,
  Digits = {"0".."9"},
  CharacterList = Text.ToList(InputText),
  FirstNumber =
    List.Accumulate(
    CharacterList,
    "",
    (String,CurrentChar)=>
      if Text.Length(String) = RequiredLength then String
      else if List.Contains(Digits,CurrentChar) then String & CurrentChar
      else ""
    ) ,
  ReturnValue =
    if Text.Length(FirstNumber) = RequiredLength then FirstNumber else null
in
  ReturnValue

The function works by taking the characters of InputText from left to right, and building up a string of numbers (stopping when 7 numeric characters are accumulated), otherwise resetting to an empty string when it encounters a non-numeric character. 

Here's another idea using table grouping to group consecutive digits together (also a function):

(InputText as text) => 
  CharacterList = Text.ToList(InputText),
  CharacterTable = Table.FromList(CharacterList, Splitter.SplitByNothing(), type table[Character = text], null, ExtraValues.Error),
  AddedIndex = Table.AddIndexColumn(CharacterTable, "Index", 1, 1),
  AddedDigitFlag = Table.AddColumn(AddedIndex, "Digit", each List.Contains({"0".."9"},[Character]), type logical),
  DigitGroups = Table.Group(AddedDigitFlag, {"Digit"}, {{"Number", each Text.Combine(Table.Sort(_,{"Index"})[Character]), type text}}, GroupKind.Local),
  FilterNumbersLength7 = Table.SelectRows(DigitGroups, each [Digit] = true and Text.Length([Number])=7),
  FirstNumber = try FilterNumbersLength7{0}[Number] otherwise null
in
  FirstNumber

Another option might be using some R code to find text matching an appropriate regular expression.

Regards,

Owen :)