Retrieve Numbers after each # in a sheet

prixle

New Member
Joined
Oct 3, 2014
Messages
1
How do I either:

(a) retrieve the numbers after each # in a sheet (i.e. if it's #143 ==> 143) and put them on a separate sheet, OR
(b) hide all information on a worksheet except for the numbers after each #.

In essence, I would like to create an array of each of these numbers so that I can use that array to identify those numbers as rows on a separate worksheet.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
How do I either:

(a) retrieve the numbers after each # in a sheet (i.e. if it's #143 ==> 143) and put them on a separate sheet, OR
(b) hide all information on a worksheet except for the numbers after each #.

In essence, I would like to create an array of each of these numbers so that I can use that array to identify those numbers as rows on a separate worksheet.
Questions...

1) I am guessing there is other text in the cells along with your #numbers... will the character after the number always be a space (unless it is at the end of the text, of course)?

2) Are we talking one column of data containing those #numbers per sheet?

3) Where do want numbers after the # sign "retrieved" to... a VBA array OR an array of cells on a worksheet (which worksheet, what cells)?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,612
Messages
5,523,896
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top