returning value from large table

[Richardeg]

I import an extract from a database and am looking for updates in comments
The problems are many
All the columns in the extrract are called Comments
The problem is that they currently run from Columns LU to ON in the extract
The rows only have data in them if there is an update
But for each comment it adds in in the next column, and if the comments keep adding it would be then 00, OP etc

I only want returned the first 11 characters of each column ie 08/SEP/2020. It is always 11 and the date

So basically I want to look up anything that is in any of the columns called Comments and then go to the last cell that has a comment in it and return the first 11 characters. or

So if in row 1 the comment is in column LU it would find the first and only comment and return the 11 characters
If in row 2 the comment is in MN (meaning there had been multiple comments added) then it would go all the way through the columns to get the last one and then return the date
If however another comment column was added as there are too many comments, it would still identify it in the column call comments albeit now OP, OQ etc and still return
So maybe it is go to the comments columns from Right to left and if blank keep going left.
I don't know how or if that is even possible
Any thoughts?

 

[Richardeg]

Can we mix this up a bit
Each cell that I am looking at has the text
DD/MMM/YY HH:MM AM;name;
So now I have the formula to return the date by using left
The ; only appears in the text as above.
Any suggestions how to return the name listed between the two ; ?

 

[Peter_SSs]

Try
=SUBSTITUTE(REPLACE(LOOKUP("z",LU2:ZZ2),1,18,""),";","")

That is based on ..

DD/MMM/YY HH:MM AM;name;

That is, a 2-digit year in the date. In post #1 you indicated 4-digit year

I only want returned the first 11 characters of each column ie 08/SEP/2020. It is always 11 and the date

If it is a 4-digit year then change the 18 in my formula to 20.

 

[Richardeg]

Try
=SUBSTITUTE(REPLACE(LOOKUP("z",LU2:ZZ2),1,18,""),";","")

That is based on ..
That is, a 2-digit year in the date. In post #1 you indicated 4-digit year


If it is a 4-digit year then change the 18 in my formula to 20.

Thanks Peter
IT rerturned the name. There is other text after the second ; which is in the original cell
It returned the name between the ; and removed the ; but has the additional text if that makes sense

 

[Peter_SSs]

There is other text after the second ; which is in the original cell

Ah, nothing in your one example.

=TRIM(MID(SUBSTITUTE(LOOKUP("z",LU2:ZZ2),";",REPT(" ",100)),100,100))

 

[Richardeg]

Yes that worked. Sorry. I was concentrating more on the name between the ; rather than the full text string
Thank you

 

[Peter_SSs]

Glad we got there in the end.