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

[yoda_pooh]

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

 

[konew1]

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.

 

[konew1]

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)))

 

[yoda_pooh]

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?

Thank you for your previous reply.

 

[konew1]

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

 

[yoda_pooh]

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

 

[konew1]

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