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?

 

[sandy666]

you can use formula with functions eg. MID, SEARCH few times but maybe someone else will give you such a solution
and of course it's possible with PQ, Formula and even vba

 

[jwdemo]

Thanks for your help thus far! I'd be up for formula or vba options

 

[alansidman]

There may be a VBA solution, but that type of solution is not in my wheel house.

 

[sandy666]

Thanks for your help thus far! I'd be up for formula or vba options

sure
1. if you upgrade Office to the higher version there will be Power Query by default (so your GM somehow remove built-in PQ ?)
2. PQ is more secure than vba because PQ cannot delete anything

 

[CA_Punit]

Meat:FZN:Retail Ready:160501

Is your number always placed at the end???
Is your number always 6 digit??

 

[jwdemo]

Is your number always placed at the end???
Is your number always 6 digit??

Yes, always at the end.
Yes, always six digits.

 

[sandy666]

Yes, always at the end.
Yes, always six digits.

=RIGHT(A1,6)
then drag down
A1 is a first cell where your text sit

if you want this as number use: =--RIGHT(A1,6)

 

[CA_Punit]

=right(cELL,6)+0

 

[jwdemo]

That did it! Thank you!!

 

[sandy666]

would be fine if you'll be addressing to whom is your post because we don't know which solution is chosen
our posts are usual to you