• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Converting text into numbers

A

amit dev thapa

Member
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
 

Attachments

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 <<<
Code: 
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"
 

Attachments

Last edited by a moderator:
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
 
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
 

Attachments

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
 

Attachments

You must log in or register to reply here.
Back
Top