Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

First position with no-numeric character

Hi,

 

I have the following problem:

 

I have a string similar to the following"12345abc". So, I have to locate the first non-numeric character (in this case the letter A at position 6) and know how many characters I have before this, so that in case its length is less than 7, fill in with the character 0 ahead until it reaches a length 7 , with the following string left: "0012345".

 

Is there a function that can be done directly to me, or do I have to do it via M code?

 

With the SEARCH or FIND functions I have no way to do it directly.

 

Thanks in advance.

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi!

     

    First of all thank you for your help, Mariusz .

    This solution is not valid. I need that if the code is 1234ABC, I will effectively return 0001234 and if the code is 1234ABC12 I will not return 0123412 as it currently does with the function you provided me.

    Finally I managed to do it with IF functions nested inside each other. The function is as follows:

     

    ArticuloOFPadreCorregido = IF(NOT ISERROR(VALUE(LEFT(OFs[Articulo Proyecto];7)));LEFT(OFs[Articulo Proyecto];7);
    IF(NOT ISERROR(VALUE(LEFT(OFs[Articulo Proyecto];6)));"0"&LEFT(OFs[Articulo Proyecto];6);
    IF(NOT ISERROR(VALUE(LEFT(OFs[Articulo Proyecto];5)));"00"&LEFT(OFs[Articulo Proyecto];5);
    IF(NOT ISERROR(VALUE(LEFT(OFs[Articulo Proyecto];4)));"000"&LEFT(OFs[Articulo Proyecto];4);
    IF(NOT ISERROR(VALUE(LEFT(OFs[Articulo Proyecto];3)));"0000"&LEFT(OFs[Articulo Proyecto];3);
    IF(NOT ISERROR(VALUE(LEFT(OFs[Articulo Proyecto];2)));"00000"&LEFT(OFs[Articulo Proyecto];2);
    IF(NOT ISERROR(VALUE(LEFT(OFs[Articulo Proyecto];1)));"000000"&LEFT(OFs[Articulo Proyecto];1);OFs[Articulo Proyecto])))))))

    Regards!

2 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi Anonymous 

    Please try using the below function in query editor.

    ( #"Text to Transform" as text ) => let
    textToList = Text.ToList( #"Text to Transform" ),
    selectValuesFromList = 
        List.Select( 
            textToList, 
            each Value.Is( 
                Value.FromText( _ ), 
                type number 
            ) 
        ), 
    combineListToText = Text.PadStart( Text.Combine( selectValuesFromList ) , 7, "0" )
in
    combineListToText

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi!

       

      First of all thank you for your help, Mariusz .

      This solution is not valid. I need that if the code is 1234ABC, I will effectively return 0001234 and if the code is 1234ABC12 I will not return 0123412 as it currently does with the function you provided me.

      Finally I managed to do it with IF functions nested inside each other. The function is as follows:

       

      ArticuloOFPadreCorregido = IF(NOT ISERROR(VALUE(LEFT(OFs[Articulo Proyecto];7)));LEFT(OFs[Articulo Proyecto];7);
      IF(NOT ISERROR(VALUE(LEFT(OFs[Articulo Proyecto];6)));"0"&LEFT(OFs[Articulo Proyecto];6);
      IF(NOT ISERROR(VALUE(LEFT(OFs[Articulo Proyecto];5)));"00"&LEFT(OFs[Articulo Proyecto];5);
      IF(NOT ISERROR(VALUE(LEFT(OFs[Articulo Proyecto];4)));"000"&LEFT(OFs[Articulo Proyecto];4);
      IF(NOT ISERROR(VALUE(LEFT(OFs[Articulo Proyecto];3)));"0000"&LEFT(OFs[Articulo Proyecto];3);
      IF(NOT ISERROR(VALUE(LEFT(OFs[Articulo Proyecto];2)));"00000"&LEFT(OFs[Articulo Proyecto];2);
      IF(NOT ISERROR(VALUE(LEFT(OFs[Articulo Proyecto];1)));"000000"&LEFT(OFs[Articulo Proyecto];1);OFs[Articulo Proyecto])))))))

      Regards!