I have a sheet "Imported Data"
I need to extract a number to the left of text like "AVG EMP/MTH" that occurs for the first time in Col I on sheet "Imported Data"
I set of a formula as follows , but it returns a blank
It would be appreciated if someone could amend my formula
Please Login or Register to view this content.
Try this:
Please Login or Register to view this content.
thanks for the help. Formula works 100%
You can also use Filter function according to your office version
Formula:
Please Login or Register to view this content.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Another alternative:
=-LOOKUP(1,-LEFT(INDEX('Imported Data'!I:I,MATCH("*AVG*EMP/MTH*",'Imported Data'!I:I,0)),ROW($1:$100)))
all of the formulae take into account that there's a double space after AVG in the first instance of:
AVG EMP/MTH instead of
AVG EMP/MTH
shukla's may affect performance as this bit: ISNUMBER(SEARCH("*AVG*EMP/MTH*",'Imported Data'!I:I)) searches ALL 1,000,000-plus rows.
Last edited by Glenn Kennedy; 03-23-2024 at 03:54 AM.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Thanks for the help Glenn& Shukla
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks