extract cell contents (words) from an adjacent cell (forumla)

Dig Digger

New Member
Joined
May 4, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello, and thanks for your worthy responses.
I have a PDF (words and tables) 700 pages.
This has been converted successfully to an excel file.

I'd like help understanding how to extract a cell contents (words) adjacent to key search phrases.
For example, you'll see in the attached image, a table. A similar table appears on each page amongst paragraphs for the 700 pages or now the long excel sheet.
I'd like a formula (on sheet 2 of the file w/extracted data) that extracts each - in this case "Job Title" i.e. 'Technical Service Manager'
So "Job Title" appears around 700 times - I'm looking to extract a list of all the actual titles.

I believe I'm aware of how to extract a cell content, but am not sure how to do the above...
Thanks again for any help,
Cheers Dave
 

Attachments

  • Capture.JPG
    Capture.JPG
    54.6 KB · Views: 10

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Welcome to the MrExcel board!

For the future, I suggest that you investigate XL2BB for providing small sample data to make it easier for helpers by not having to manually type out sample data to test with.

Is it something like this you are after?

Dig Digger.xlsm
AB
1
2Job TitleJob Title 1
3Job TypeJob Type 1
4LocationLocation 1
5Job TitleJob Title 2
6Job TypeJob Type 2
7LocationLocation 2
8Job TitleJob Title 3
9Job TypeJob Type 3
10LocationLocation 3
Sheet1



Dig Digger.xlsm
A
1Job Title
2Job Title 1
3Job Title 2
4Job Title 3
5
Sheet2
Cell Formulas
RangeFormula
A2:A4A2=FILTER(Sheet1!B2:B1000,Sheet1!A2:A1000=A1)
Dynamic array formulas.
 
Upvote 0
My apologies, I will check out that reference. This formula worked a treat, I was off by a mile. Thank you. One other question whilst I have you. This may be a silly question, but the formula you posted and I copied to test it out is obviously repeated throughout the column. However, when I look at the first formula say A2 it appears as normal, but all the following repeats of that formula are greyed out, and if you click into the attention bar where it is greyed out the formula disappears... Can I ask what is the process used for copying the same formula cell after cell? Cheers Dave
 
Last edited by a moderator:
Upvote 0
This formula worked a treat,
Good news. Glad it worked for you. :)

This is a "dynamic array" formula. It only needs to be entered in a single cell and Excel works out how many rows and/or columns the results need to "spill" into. This feature was introduced in late 2018 and is only available in Microsoft 365.

You can find out some more about it here
 
Upvote 0
Solution
Excellent thanks so much... I genuinely appreciate this. Cheers Dave
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top