Searching Row for Value of "n" digits and returning cell address

yoda_pooh

Board Regular
Hello,

Our software extract for our data base sucks and values do not align properly if all entries do not have tge exact same number of fields with data in them.

I have always done the alignment in excel manually. I can find where the data starts to misalign and would like to do this automatically.

There is an id number for each entry (row) that is 6 digits long. How can I search a row for a 6 digit number and return the address of that cell? i can use this address as a starting point and use the offset function to do so alignment from there (I hope).

Thank you

Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
This will return the number of the first row that contains a 6 digit number.
=MIN(IF(LEN(P2:P11)=6,ROW(P2:P11)))
You may need to use >5 instead of =6 if you are worried about longer numbers (7 digits)
It is an array formula to be eentered with Ctrl+Shift+Enter, not just Enter.

My posting assumed your data was in a column, but on re-reading your question I see it is in a row. Just change the range accordingly, and change "Row" to "Column"
=MIN(IF(LEN(A2:P2)=6,COLUMN(A2:P2)))

Thank you konew1. That almost has me where I need to be. The only issue is, it needs to pick up the first 6 digit number entry. The above formula also returns 6 character text entries. Would adding the ISNUMBER function work so it only searches for numerical values?

This seems to work for me, using your ISNUMBER suggestion. Note also I have put "-1" . The first formula was 1 row out.
=MIN(IF(ISNUMBER(P2:P11),IF(LEN(P2:P11)=6,ROW(P2:P11))))-1

Thank you, that is working great. I don't seem to need the "-1" though. Now for the long and tedious part.

Thanks for the ack. You briefly described the long and tedious part in your first posting. It seems to me that you need to find data >6 characters and split it into 2 fields in adjacent cells. Possibly have to move other data to make space. This kind of thing can be done on by vba code. i suggest you post that as a new query and see what help you get. I don't write code

Replies
7
Views
370
Replies
9
Views
436
Replies
5
Views
175
Replies
0
Views
241
Replies
2
Views
695

1,203,262
Messages
6,054,428
Members
444,725
Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back