Pulling Specific Data from a Cell

Question

Hello,

I have a column of cells that contain data in them. I'm trying to pull only part of that data to a different cell for use in an XLOOKUP formula. The data contains aircraft and a date last flown in. For example:

UH-60L: 29 Jan 2025

UH-1H: 29 Apr 2009

UH-1V: 29 Apr 2009

UH-60A: 29 Jan 2025

That data is all in one cell, E69, and the destination is L69. I would like only one date of one aircraft, the UH-60L. L69 should therefore look like this:

29 Jan 2025

Is there a formula I can apply without having to do it manually?

Answer 1

Hi Andrew Buck2,

Thanks for your valuable feedback and sharing updates with us and I really appreciate your effort and your precious time doing. 

And I appreciate your kind words.

If the data in cell E69 contains line breaks:

=TRIM(MID(E69, SEARCH("UH-60L:", E69) + 8, SEARCH(CHAR(10), E69 & CHAR(10), SEARCH("UH-60L:", E69)) - SEARCH("UH-60L:", E69) - 8))

Or if you please send copy of your document file to me so that I can look from my side, and I will check this behavior and help you to fix and verify the result with you.

If there is confidential information, then you can send it to me in Private Message(PM). you can access PM from the top right corner of this page, as shown in image below:

Your understanding and patience will be highly appreciated. I hope that you are keeping safe and well!

Sincerely,

Waqas Muhammad

Answer 2

Dear respected  Andrew Buck2,

Good day! Thank you for posting to Microsoft Community. We are happy to help you.

As per your description,

=IFERROR(TRIM(MID(E69, SEARCH("UH-60L:", E69) + LEN("UH-60L:"), 12)), "")

If there is any misunderstanding, I apologize and please feel free to post back to us with more detailed information for better understanding and guiding you further.

Please understand that our initial reply may not always immediately resolve the issue. However, with your help and more detailed information, we can work together to find a solution.

I'm sorry and I apologize for that.

Appreciate your patience and understanding and thank you for your time and cooperation.

Sincerely,

Waqas Muhammad

Answer 3

Thank you for the fast reply. When I copy and paste the formula, L69 is blank. It still shows the formula in the bar, but the cell itself is blank. If I try to type it manually, Excel thinks it's not a formula.

The only thing I can think of is that this data is pulled from a records software. It inputs the data into a cell with each aircraft having its own line, as well as a blank line at the end, like so:

UH-60L: 29 Jan 2025

UH-1H: 29 Apr 2009

UH-1V: 29 Apr 2009

UH-60A: 29 Jan 2025

(blank line here)