Finding and Retrieving Numbers and Names from a Large Workbook

Hello,

I would like to recover and make usable some information that's within a huge spreadsheet with many rows. What I'd like to do is add rows to the top of the spreadsheet and run two functions that will display the small amount of useful information in those new rows.

All the useful information is within brackets, usually all in one set of brackets per column. The information has two components: one or more numbers of varying length and one or more words. Ideally one cell in each of my new rows would display the names currently within the brackets and one cell would list the numbers.

The numbers are of different length so I can't just fetch the first x digits after the opening bracket with =LEFT. The other thing that I can't figure out is how to search when the important cell is in a different row from one column to the next.

Thanks in advance for your help. I'm open to really taking the time to learn about this, so even pointing me to the appropriate instructional video would be a huge help because I am at a loss.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Edit: The UDF Personal.xlsb!DigitsOnly will not work upon opening the attached file. It will only work after installing.

The Personal.xlsb! part just happens to be the macro file I have this one stored in. You may want to change that part.



Here's an attempt. The Name: formula is in C2:E2 of the new sheet I added. If it works 75% of the time I'll be shocked. I am not confident

I found a consistent pattern in the examples. The ", " and number(s?) combinations mid-text threw me. This formula worked for that but only because I

knew it was there.

The second formula C3:E3 has the same limitations. In didn't try to take the "7, 1 " part any further than I did.

For the third formula C4:E4 I used a User Defined Function called DigitsOnly. I got this one from Rick Rothstein. I've included the code in

this post. I modified it slightly so that it only returns numbers as text. I does not retain the ", "s or "-"s. Perhaps you can modify the

code below to address that.

File is attached.

Please Login or Register  to view this content.

I hope this helps somewhat.