hi, i have a report coming from an accounting system into excel, depending on the type of transaction (sales invoice, sales credit memo, purchase invoice, purchase cr memo, journal etc) the reference number will be in on of 4 colums, call them n2 - q2.
What i would like to do is have a formula that will return the first instance of a 7 digit number beginning 10 that it comes across.
how best to achieve this?
thanks in advance
You'd be better of uploading an example which shows the result you expect.
The statement 'first instance' is open to interpretation.
First instance in what?
By row down a column then across columns or by column across the columns and then down the rows?
Oh, incidentally. Would you edit your details to show your location please. It is often useful information when formulating a response to stuff like date arithmetic or deciding whether regional settings may be relevant.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
hi
location updated, its Dublin Ireland.
What i want is a formula in the cell in yellow to look across the cells in blue and select the 8 digit number beginning 10 (the first time it comes across it ideally)
is that a little clearer?
thanks
Hi,
One way
Formula:
Please Login or Register to view this content.
thanks, but that will just return the first number it comes across correct, in the data there may be 4 different types of numbers across the columns, i want to be able to specify an 8 digit number beginning with 10, is that possible?
Please try at J2
=INDEX(K2:O2,MATCH(1,INDEX((LEFT(K2:O2,2)="10")*(LEN(K2:O2)=8),),))
thanks ill try that
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks