Look for a value in a multiple line cells

AliaNi

New Member
Joined
Oct 19, 2022
Messages
32
Office Version
  1. 2010
Platform
  1. Windows
Hi

I have a dataset of 200 rows.
Each cell has multiple lines, I want to write a formula that finds the line which has a specific word, and then get the corresponding value from the cell next to it. Is that possible?

Row 1:
Cell 1 Cell 2
A v
B d
C e

Row 2:
Cell 1 Cell 2
B d
C e

I want to find the vals (d) from cell 2 based on cell 1 (B)

Is this possible?
 

Attachments

  • Capture.PNG
    Capture.PNG
    2.3 KB · Views: 4
I just uploaded XL2BB with the expected results.
Thank you! :)

Try this

22 10 20.xlsm
ABCDE
1StartedEndedScheduled
2Scheduled Started Ended10:00 am 10:15 am 11:00 am10:15:00 AM11:00:00 AM10:00:00 AM
3Started Ended3:00 pm 4:00 pm3:00:00 PM4:00:00 PM 
4Started Ended Scheduled1 pm 2 pm 12 pm 1:00:00 PM2:00:00 PM12:00:00 PM
AliaNi
Cell Formulas
RangeFormula
C2:E4C2=IFERROR(TRIM(MID(SUBSTITUTE($B2,CHAR(10),REPT(" ",100)),INT(FIND(C$1,SUBSTITUTE($A2,CHAR(10),REPT(" ",100)))/100)*100+1,100))+0,"")
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
That sort of thing just blows my mind... :eek:
Even if I step through that I doubt I'll understand it. Code is enough of a challenge for my old brain.
 
Upvote 0
Thank you! :)

Try this

22 10 20.xlsm
ABCDE
1StartedEndedScheduled
2Scheduled Started Ended10:00 am 10:15 am 11:00 am10:15:00 AM11:00:00 AM10:00:00 AM
3Started Ended3:00 pm 4:00 pm3:00:00 PM4:00:00 PM 
4Started Ended Scheduled1 pm 2 pm 12 pm 1:00:00 PM2:00:00 PM12:00:00 PM
AliaNi
Cell Formulas
RangeFormula
C2:E4C2=IFERROR(TRIM(MID(SUBSTITUTE($B2,CHAR(10),REPT(" ",100)),INT(FIND(C$1,SUBSTITUTE($A2,CHAR(10),REPT(" ",100)))/100)*100+1,100))+0,"")
Omg it workedd thank you. Can you explain what it does?
 
Upvote 0
Omg it workedd thank you.
Good news! You're welcome.

Can you explain what it does?
Roughly, it does this.
It spreads the sections in each cell of the row out with 100 spaces between each bit.
So it is like
"Scheduled...............Started...............Ended" and
"10:00 am.................10:15 am............11:00 am"
though even more spread out.

Then it looks at the spread-out first column A value to find if the heading (eg "Started" is right at the beginning or about 100 characters from the start or about 200 characters from the start. Depending on that result (say it was about 100 characters) then it grabs a heap of characters from about the same position (100 characters from the start) of the spread-out column B text. This should included the correct answer but it will be surrounded by a lot of spaces. These spaces are then trimmed off and the +0 turns the result back from a text value to an actual time value (number)
 
Upvote 0
Good news! You're welcome.


Roughly, it does this.
It spreads the sections in each cell of the row out with 100 spaces between each bit.
So it is like
"Scheduled...............Started...............Ended" and
"10:00 am.................10:15 am............11:00 am"
though even more spread out.

Then it looks at the spread-out first column A value to find if the heading (eg "Started" is right at the beginning or about 100 characters from the start or about 200 characters from the start. Depending on that result (say it was about 100 characters) then it grabs a heap of characters from about the same position (100 characters from the start) of the spread-out column B text. This should included the correct answer but it will be surrounded by a lot of spaces. These spaces are then trimmed off and the +0 turns the result back from a text value to an actual time value (number)
Thank you so much. Does it work if I had dates instead of time?
 
Upvote 0
Power Query can solve this. Split column A by space as a delimiter, column B by "m " as delimiter. Then you end up with something like this:

1666249215164.png


From here on, you can sort things out using a series of conditional columns. For example this:

1666249404052.png


will get you this:

1666249481859.png


You will also need to clean up the various time formats, which may require more conditional columns.
 

Attachments

  • 1666249375654.png
    1666249375654.png
    14.1 KB · Views: 1
Upvote 0
You are very welcome. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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