none
Alternative to Splitter.SplitTextByCharacterTransition RRS feed

  • Question

  • I have old version of o365 excel-2016. As a result, the following function is not being recognised. What would be alternative work around for the following function? Many Thanks.

    Sunday, January 19, 2020 10:47 PM

Answers

  • Hi Lz: Thx for looking into this solution. Please see below and let me know if that make sense. I just realised that there are some existing solutions in various forums on how to extract number from letters. However, haven't come across any solution utilising Splitter.Split type functions except for Splitter.SplitTextBy CharacterTransition.

    One way to extract the number would be:

    CustCol = Table.AddColumn(prevstep, "XXXX", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([ColName]),each if Value.Is(Value.FromText(_), type number) then _ else null))))

    From:

    To:

    • Marked as answer by M.Awal Tuesday, January 21, 2020 3:08 AM
    Monday, January 20, 2020 11:35 AM

All replies

  • Hi Awal

    An Error message is always a good info. The context and the expectation is also important otherwise risk exists you'll get inappropriate guidance. So, are you in Case1 where only 1 split is required or in Case2 where it's > 1?

    Case1 isn't too difficult, Case2 I have at the moment no clue how to achieve this 

    Monday, January 20, 2020 11:06 AM
  • Hi Lz: Thx for looking into this solution. Please see below and let me know if that make sense. I just realised that there are some existing solutions in various forums on how to extract number from letters. However, haven't come across any solution utilising Splitter.Split type functions except for Splitter.SplitTextBy CharacterTransition.

    One way to extract the number would be:

    CustCol = Table.AddColumn(prevstep, "XXXX", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([ColName]),each if Value.Is(Value.FromText(_), type number) then _ else null))))

    From:

    To:

    • Marked as answer by M.Awal Tuesday, January 21, 2020 3:08 AM
    Monday, January 20, 2020 11:35 AM
  • OK Awal

    Understand what the code you posted does. However, the remaining question is: what do you need to achieve?

    Example below implements the code you posted, against [column1]. Result => All digits are extracted:

    Is this what you expect? If so you already have the solution. If this isn't what you want, please post a couple of examples of what you have in [column1] (or whatever it's name is) and next it, what you expect

    Monday, January 20, 2020 12:17 PM
  • Yes. This is what I what I want to achieve. Thx.
    Monday, January 20, 2020 7:53 PM
  • OK Awal so please mark as answer your reply with the code you found & posted - Thx
    Monday, January 20, 2020 10:36 PM