Harvesting unique figures in multiple column cells

**kayode** · 09-10-2017, 03:40 PM

I have a 19+ digit variant number always starting with 6................-... The "-" is always on number 19 slot after which a digit or two or three or four digits follows. These unique numbers are always exist once within column H to X in each row of data. Sometimes, i could have 60 thousand row from which to extract this value into one specific column. Definitely, l need formular help to achieve this task. Can someone help me please with something that will do this and still give me the result as editable values?

**Glenn Kennedy** · 09-10-2017, 04:05 PM

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**kayode** · 09-11-2017, 11:33 AM

I have send the sample please and I hope you"ll receive it. You Know, my first time. I hope it will get there

**MrShorty** · 09-11-2017, 11:58 AM

These unique numbers are always exist once within column H to X in each row of data. Sometimes, i could have 60 thousand row from which to extract this value into one specific column.

Are you familiar with Excel's lookup functions (like INDEX() and MATCH())? If I understand what you are trying to do:

1) Use a MATCH() function to determine which column contains the desired text string (https://support.office.com/en-us/art...9-533f4a37673a ). Note that, if the third argument of the MATCH() function is 0, you can use wildcards (* or ?) to modify your search. Based on your sample data, =MATCH("6359*",A2:Q2,0) returns the column number where that number exists. Of course, this will have trouble if there are two entries in the row that start with 6359. You may need to put some time and thought in the search string you put into the MATCH() function to make sure that it will return the correct value. (In the first row of your sample, it returns 4)
2) Use the INDEX() function to extract the value from the data (https://support.office.com/en-us/art...2-b56b061328bd ). Perhaps something like =INDEX(A2:Q2,result of match function).
3) Copy formulas down as far as needed.

something that will do this and still give me the result as editable values?

I am not sure I understand what you mean by this. If you want, you can use the formulas above, then do a copy -> paste special -> as values to get the result as a value that you can then edit. Is that what you mean?