First position with no-numeric character

[Anonymous]

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.

[Mariusz]

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.

[Anonymous · Accepted solution]

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!