Extract individual lines from cell

SaraWitch

SaraWitch

Active Member
Joined
Sep 29, 2015
Messages
370
Office Version
  1. 365
Platform
  1. Windows
Hello peeps,

I have a cell with as many as six lines of data and want to extract values line by line. I have a formula to extract the first line and everything after the first line, but this is returning all five lines. Is there a way to extract just line 2, line 3, line 4...etc.?
¦ MrExcel Queries.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1AddressesDate endedAddress 1Date fromDate toAddress 2Would likeDate fromDate toAddress 3Would likeDate fromDate toAddress 4Would likeDate fromDate toAddress 5Would likeDate fromDate toAddress 6Would likeDate fromDate to
2Gnome Cave, High Cloud - 25/08/2022 Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/202404/01/2024Gnome Cave, High Cloud - 25/08/202225/08/202230/12/2023Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/2024Sky Apartments, Stormville - 08/08/202208/08/202231/12/2023Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/2024Low Heaven, Angel Cove - 06/01/202208/08/202201/01/2024Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/2024Ocean Whirlpool, Seagate - 10/10/202308/08/202202/01/2024Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/2024Valley Deep, Grasslands - 11/11/202308/08/202203/01/2024Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/2024Forest Mound, Woodland - 02/01/202408/08/202204/01/2024
Extract lines
Cell Formulas
RangeFormula
C2C2=TEXTBEFORE($A2,CHAR(10))
D2D2=IFERROR(DATEVALUE(MID(C2,SEARCH("/??/",C2,1)-2,10)),"")
E2,T2,O2,J2E2=IF(I2="",$B2,J2-1)
G2,AA2,V2,Q2,L2G2=TEXTAFTER($A2,CHAR(10))
I2,AC2,X2,S2,N2I2=IFERROR(DATEVALUE(MID(G2,SEARCH("/??/",G2,1)-2,10)),"")
Y2Y2=IF(X2="","",IF(AC2>0,AD2-1,$B2))
AD2AD2=IF(AC2="","",IF(AG2>0,AH2-1,$B2))

Any help would be appreciated :)
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
I've also noticed that if I only have five lines of text, my date formula creates errors... :unsure:
¦ MrExcel Queries.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1AddressesDate endedAddress 1Address 1Date fromDate toAddress 2Would likeDate fromDate toAddress 3Would likeDate fromDate toAddress 4Would likeDate fromDate toAddress 5Would likeDate fromDate toAddress 6Would likeDate fromDate to
2Gnome Cave, High Cloud - 25/08/2022 Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/202404/01/2024Gnome Cave, High Cloud - 25/08/2022Gnome Cave, High Cloud - 25/08/202225/08/202230/12/2023Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/2024Sky Apartments, Stormville - 08/08/202208/08/202231/12/2023Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/2024Low Heaven, Angel Cove - 06/01/202208/08/202201/01/2024Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/2024Ocean Whirlpool, Seagate - 10/10/202308/08/202202/01/2024Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/2024Valley Deep, Grasslands - 11/11/202308/08/202203/01/2024Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/2024Forest Mound, Woodland - 02/01/202408/08/202204/01/2024
3Gnome Cave, High Cloud - 25/08/2022 Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/202304/01/2024Gnome Cave, High Cloud - 25/08/2022Gnome Cave, High Cloud - 25/08/202225/08/2022#VALUE!Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023Sky Apartments, Stormville - 08/08/202208/08/2022#VALUE!Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023Low Heaven, Angel Cove - 06/01/202208/08/2022#VALUE!Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023Ocean Whirlpool, Seagate - 10/10/202308/08/2022#VALUE!Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023Valley Deep, Grasslands - 11/11/202308/08/2022#VALUE!Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023  
Extract lines
Cell Formulas
RangeFormula
C2:D3C2=TEXTBEFORE($A2,CHAR(10))
E2:E3,AD2,Y2:Y3,T2:T3,O2:O3,J2:J3E2=IFERROR(DATEVALUE(MID(C2,SEARCH("/??/",C2,1)-2,10)),"")
F2:F3,U2:U3,P2:P3,K2:K3F2=IF(J2="",$B2,K2-1)
Z2:Z3Z2=IF(Y2="","",IF(AD2>0,AE2-1,$B2))
AE2:AE3AE2=IF(AD2="","",IF(AH2>0,AI2-1,$B2))
AD3AD3=IFERROR(DATEVALUE(MID(AC3,SEARCH("/??/",AC3,1)-2,10)),"")
H2:H3,AB2:AB3,W2:W3,R2:R3,M2:M3H2=TEXTAFTER($A2,CHAR(10))
 
Upvote 0
You could use INDEX and TEXTSPLIT
=INDEX(TEXTSPLIT($A2,CHAR(10)),1)
=INDEX(TEXTSPLIT($A2,CHAR(10)),2)
=INDEX(TEXTSPLIT($A2,CHAR(10)),3)
=INDEX(TEXTSPLIT($A2,CHAR(10)),4)
=INDEX(TEXTSPLIT($A2,CHAR(10)),5)
 
Upvote 0
Solution
What is the Date supposed to be if there isn't 5 values? 1 day earlier than the last date?
 
Upvote 0
Wow, that's fabulous! 😁 Thank you, Scott.

I've updated the table with a few more examples and put what the dates should be:
¦ MrExcel Queries.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
1AddressesDate endedAddress 1Date fromDate toDate to should beAddress 2Date fromDate toDate to should beAddress 3Date fromDate toDate to should beAddress 4Date fromDate toDate to should beAddress 5Date fromDate toDate to should beAddress 6Date fromDate toDate to should be
2Gnome Cave, High Cloud - 25/08/2022 Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/2023 Forest Mound, Woodland - 02/01/202410/01/2024Gnome Cave, High Cloud - 25/08/2022Gnome Cave, High Cloud 25/08/202205/01/202405/01/2024Sky Apartments, Stormville - 08/08/2022Sky Apartments, Stormville 08/08/202206/01/202406/01/2024Low Heaven, Angel Cove - 06/01/2022Low Heaven, Angel Cove 06/01/202207/01/202407/01/2024Ocean Whirlpool, Seagate - 10/10/2023Ocean Whirlpool, Seagate 10/10/202308/01/202408/01/2024Valley Deep, Grasslands - 11/11/2023Valley Deep, Grasslands 11/11/202309/01/202409/01/2024Forest Mound, Woodland - 02/01/2024Forest Mound, Woodland 02/01/202410/01/202410/01/2024
3Gnome Cave, High Cloud - 25/08/2022 Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/2023 Valley Deep, Grasslands - 11/11/202310/01/2024Gnome Cave, High Cloud - 25/08/2022Gnome Cave, High Cloud 25/08/2022#VALUE!06/01/2024Sky Apartments, Stormville - 08/08/2022Sky Apartments, Stormville 08/08/2022#VALUE!07/01/2024Low Heaven, Angel Cove - 06/01/2022Low Heaven, Angel Cove 06/01/2022#VALUE!08/01/2024Ocean Whirlpool, Seagate - 10/10/2023Ocean Whirlpool, Seagate 10/10/2023#VALUE!09/01/2024Valley Deep, Grasslands - 11/11/2023Valley Deep, Grasslands 11/11/2023#VALUE!10/01/2024    BLANK CELL
4Gnome Cave, High Cloud - 25/08/2022 Sky Apartments, Stormville - 08/08/2022 Low Heaven, Angel Cove - 06/01/2022 Ocean Whirlpool, Seagate - 10/10/202310/01/2024Gnome Cave, High Cloud - 25/08/2022Gnome Cave, High Cloud 25/08/2022#VALUE!07/01/2024Sky Apartments, Stormville - 08/08/2022Sky Apartments, Stormville 08/08/2022#VALUE!08/01/2024Low Heaven, Angel Cove - 06/01/2022Low Heaven, Angel Cove 06/01/2022#VALUE!09/01/2024Ocean Whirlpool, Seagate - 10/10/2023Ocean Whirlpool, Seagate 10/10/2023#VALUE!10/01/2024    BLANK CELL    BLANK CELL
5Gnome Cave, High Cloud - 25/08/202210/01/2024Gnome Cave, High Cloud - 25/08/2022Gnome Cave, High Cloud 25/08/2022#VALUE!10/01/2024    BLANK CELL    BLANK CELL    BLANK CELL    BLANK CELL    BLANK CELL
Extract lines
Cell Formulas
RangeFormula
C2:C5C2=IFERROR(INDEX(TEXTSPLIT($A2,CHAR(10)),1),"")
D2:D5D2=IFERROR(LEFT(A2,FIND("-",A2)-1),"")
E2:E5,AD2,Y2:Y5,T2:T5,O2:O5,J2:J5E2=IFERROR(DATEVALUE(MID(C2,SEARCH("/??/",C2,1)-2,10)),"")
F2:F5,Z2:Z5,U2:U5,P2:P5F2=IF(E2="","",IF(J2>0,K2-1,$B2))
H2:H5H2=IFERROR(INDEX(TEXTSPLIT($A2,CHAR(10)),2),"")
I2:I5,AC2:AC5,X2:X5,S2:S5,N2:N5I2=IFERROR(LEFT(H2,FIND("-",H2)-1),"")
K2:K5K2=IF(J2="","",IF(P2>0,Q2-1,$B2))
M2:M5M2=IFERROR(INDEX(TEXTSPLIT($A2,CHAR(10)),3),"")
R2:R5R2=IFERROR(INDEX(TEXTSPLIT($A2,CHAR(10)),4),"")
W2:W5W2=IFERROR(INDEX(TEXTSPLIT($A2,CHAR(10)),5),"")
AB2:AB5AB2=IFERROR(INDEX(TEXTSPLIT($A2,CHAR(10)),6),"")
AE2:AE5AE2=IF(AD2="","",$B2)
AD3:AD5AD3=IFERROR(DATEVALUE(MID(AC3,SEARCH("/??/",AC3,1)-2,10)),"")
 
Upvote 0
In F3, I did also try:
VBA Code: 
=IF(E2=0,"",IF(J2>0,K2-1,$B2))
...but that's not working either...:unsure:
 
Upvote 0
You must log in or register to reply here.

Similar threads

SaraWitch
  • Question Question
Highlight cell if text matches in range of cells, but only if other cells are blank
2
Replies
11
Views
604
SaraWitch
SaraWitch
C
  • Solved
Extract Dates from 2 columns
Replies
2
Views
644
Peter_SSs
Peter_SSs
V
  • Solved
Finding Price from Matching Date and Yarn ID.
2
Replies
13
Views
977
Vishnu75
V
V
  • Solved
How do I create a summary report with data from a single tab?
Replies
6
Views
526
AhoyNC
A
W
  • Question Question
Selection of data in the table that is furthest in time, but correct.
Replies
1
Views
309
Wojciech
W

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

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