Seperate number and text from the lft

[Anonymous]

Product Column                          Needed new column as 

 288 Ham 20*5g                        Ham 20*5g 

110 Caam 10*5 g                       Caam 10*5 g

220 Ham 25*10g                        Ham 25*10g 

Bun 4" 8*12*5g                           Bun 4" 8*12*5g

SBS 200  20g                               SBS 200  20g

Thanks for your  help community 

[Anonymous · Accepted solution]

Hi @Anonymous ,

I would propose Text.PositionOfAny as illustrated in Split column based on text and numerical characters - YouTube

Please using the following formula to add a custom column:

=Text.PositionOfAny([Product Column],{"A".."Z"})

Then add a new column by extracting the original column:

=Text.Middle([Product Column], [Custom])

After removing other columns, the final output is shown below:

Here is the whole M formula:

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.PositionOfAny([Product Column],{"A".."Z"})),
    #"Inserted Text Range" = Table.AddColumn(#"Added Custom", "New Column", each Text.Middle([Product Column], [Custom]), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Text Range",{"Product Column", "Custom"})
in
    #"Removed Columns"

Please take a look at the pbix file.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello,@Anonymous , Thank you for your soution, it worked ,, but  the second  one 

Then add a new column by extracting the original column:

 should me 

=Text.Middle([Product Column],[custom])

Thanks again 

[Anonymous]

@Anonymous ,

Oh,Sorry for my carelessness!  I have edited the previous reply. Thanks😀

Best Regards,
Eyelyn Qin

[Anonymous]

Thank you @Anonymous  !!!!

[amitchandak]

@Anonymous , Try like this; if first character is text , use Text.Start, if true then Split on first space

Use the code where to check number

https://eriksvensen.wordpress.com/2018/03/06/extraction-of-number-or-text-from-a-column-with-both-text-and-number-powerquery-powerbi/

[Anonymous]

@amitchandak  , i tried  your suggestion but it gives me  new colunm either only numbers or new column only character along with g.  I would like to remove the number before each product name.....