Removing everything but numbers from cell

[jwdemo]

I have a worksheet and column B has a list of departments and supplier order numbers in it. I'm wanting to get rid of everything but the numbers.
Some examples in these cells are:

Meat:FZN:Retail Ready:160501
Local Meat:Pork:130517
Local Meat:Beef-SweetGrass:110005
Dairy:Food Service:220017


Is there a way to get this done with a macro or a formula in an adjacent column?

 

[alansidman]

Assuming all numbers to be parsed out are always located at the right of the last colon, then power query mcode as follows.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"})
in
    #"Split Column by Delimiter"

Book9
	A	B
1	Column1.1	Column1.2
2	Meat:FZN:Retail Ready	160501
3	Local Meat:Pork	130517
4	Local Meat:Beef-SweetGrass	110005
5	Dairy:Food Service	220017
Sheet2

 

[sandy666]

with Power Query

Raw	Numbers
Meat:FZN:Retail Ready:160501	160501
Local Meat:Pork:130517	130517
Local Meat:Beef-SweetGrass:110005	110005
Dairy:Food Service:220017	220017

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Numbers = Table.AddColumn(Source, "Numbers", each Text.Select([Raw],{"0".."9"}))
in
    Numbers

 

[jwdemo]

Assuming all numbers to be parsed out are always located at the right of the last colon, then power query mcode as follows.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"})
in
    #"Split Column by Delimiter"

Book9
	A	B
1	Column1.1	Column1.2
2	Meat:FZN:Retail Ready	160501
3	Local Meat:Pork	130517
4	Local Meat:Beef-SweetGrass	110005
5	Dairy:Food Service	220017
Sheet2

Thanks! How in the world do I use a "Power Query"?

 

[jwdemo]

with Power Query

Raw	Numbers
Meat:FZN:Retail Ready:160501	160501
Local Meat:Pork:130517	130517
Local Meat:Beef-SweetGrass:110005	110005
Dairy:Food Service:220017	220017

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Numbers = Table.AddColumn(Source, "Numbers", each Text.Select([Raw],{"0".."9"}))
in
    Numbers

Thanks! How in the world do I use a "Power Query"?

 

[sandy666]

first update your profile about Excel version(s)

 

[jwdemo]

Alrighty. Done.

 

[sandy666]

so you'll need download and install Power Query add-in from MS site
PowerQuery add-in for XL 2010/2013, all above has PQ built-in

if you want to learn here is more about PowerQuery aka Get&Transform

• What is PowerQuery (Get&Transform)
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference (erratum required)

 

[sandy666]

btw. here is third, different than previous two, approach with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TAD = Table.AddColumn(Source, "Numbers", each Text.AfterDelimiter([Raw], ":", {0, RelativePosition.FromEnd}), type text)
in
    TAD

Raw	Numbers
Meat:FZN:Retail Ready:160501	160501
Local Meat:Pork:130517	130517
Local Meat:Beef-SweetGrass:110005	110005
Dairy:Food Service:220017	220017

as you can see three different codes but result is the same

 

[jwdemo]

Our GM is a little hestiant about anyone downloading anything...even from Microsoft. Is there a different way to get the same result or is a power query the only answer?