Converting text into numbers

[amit dev thapa]

I have a menu file ,which i have downloaded online , all the menu items are in one column only , it is difficult to do it manually , the menu item looks like following :-

1. 7 UP (1 pcs)2. 3 Pc Boneless Chicken Strips (2 pcs)3. Chicken Zinger (1 pcs)

this is actually 7 UP is the menu item name (1 Pcs is quantiy is 1), 3 Pc Boneless Chicken strips (2 Pcs is quantiy is 2), Chicken Zinger(1 pcs is quanity 1) and secondly 1. 2. & 3. is serial number of menu item.

what i want is .. to remove 1. and 2. ,3. convert (1 Pcs) & 2 Pcs text to number 1 and 2 in different column , it should be like following :-

Menu item name Qty
7 UP 1
3 Pc Boneless strips 2
Chicken Zinger 1

Please find attached file, have a good day

Regards

Amit

 

[bluesky63]

Hi amit,

this can be done by Power Query (build the UI query logic once)

mainly using Split to column to extract Text between Â, and (

You can insert the following PQ M code in Advanced editor or read my attached solution

Rgds

>>> use code - tags <<<

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"String", type text}}),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Changed Type", "Text Between Delimiters", each Text.BetweenDelimiters([String], "1.Â", "2.Â"), type text),
    #"Inserted Text Between Delimiters1" = Table.AddColumn(#"Inserted Text Between Delimiters", "Text Between Delimiters.1", each Text.BetweenDelimiters([String], "2.Â", "3.Â"), type text),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Between Delimiters1", "Text After Delimiter", each Text.AfterDelimiter([String], "3.Â"), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Text After Delimiter",{{"Text Between Delimiters", "Item1"}, {"Text Between Delimiters.1", "Item2"}, {"Text After Delimiter", "Item3"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"String"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"String"}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Removed Columns", {{"Attribute", each Text.AfterDelimiter(_, "Item"), type text}}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Extracted Text After Delimiter", "Text Before Delimiter", each Text.BeforeDelimiter([Value], "Â ("), type text),
    #"Inserted Text Between Delimiters2" = Table.AddColumn(#"Inserted Text Before Delimiter", "Text Between Delimiters", each Text.BetweenDelimiters([Value], "(", "pc"), type text),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Text Between Delimiters2",{"Value"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns1",{{"Attribute", Text.Trim, type text}, {"Text Before Delimiter", Text.Trim, type text}, {"Text Between Delimiters", Text.Trim, type text}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Trimmed Text",{{"Text Before Delimiter", "Item Name"}, {"Text Between Delimiters", "Qty"}, {"Attribute", "S/No"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Qty", Int64.Type}})
in
    #"Changed Type1"

 

[amit dev thapa]

thanks for the prompt response.. i copied the menu in the raw source and i refresh power query .. the output is different , please find attached file, apologies not able to understand ..the power query piece

 

[bluesky63]

Hi amit, as you only given in the sample one value and I will not know other string patterns
anyway, edited and attached

 

[amit dev thapa]

thanks .. will look at it , i guess in future i just have to copy the data and add in raw sheet .. will write to you , if i need any more help ,thanks

 

[amit dev thapa]

Hi morning , it is working perfect , i found only one issue , when

1. 8Pc Smoky Grilled (1 pcs)2. Tangy Fries (2 pcs)3. Chicken Zinger (1 pcs)4. 3 Pc Boneless Chicken Strips (1 pcs) , in the output file it is picking only 3 item and whereas it has 4 item .. means in bill there may be min 1 and can be max 10.. 12

i have highlighted it .. raw source sheet ..Row 24 and 57 , output sheet row no 29-31 , row 74-76

 

[bluesky63]

Hi Sir,

Using another query method that will take care any items in the verbatim strings, I had inserted item 5 in 56 and it works, you can put in 8 items and above, let me know

Note :- this query only works as the  and pcs) delimiter are fix position

 

[amit dev thapa]

It is working , thanks for your help

Regards

Amit