Formula for removing unwanted characters

[donjamin]

Hello All,

Hoping for some help.

I have a column listing order numbers followed by a * and number, the number indicating the piece within the order - for instance 66576505*2. I need to remove the *2 to just show the order number. In some cases, there are double digit pieces within the order so not as simple as just removing the last 2 characters. If it could remove everything from the * over to the right that would be excellent.

Thanks for your help!

[maclearyj]

Hi there. I am assuming you are able to put a formula in another column to show what you want to see. If so, this formula will give the result you want - it assumes your data is in C5: =LEFT(C5,FIND("*",C5&"*")-1). If you want to replace the contents, then you will have to use vba.

[alansidman]

An alternative means is to use Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("*", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Column1.2"})
in
    #"Removed Columns"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.