• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How do you find a row within a cell with a formula?

Harry0

Member
Within a single cell has multiple lines within which I want to extract a row, ex: 3rd row.
At first I thought of using =find("^P", a1, 1) But that did not work. ^P meaning paragraph since it works in search/replace. Each cell is completely different.

Cell can say

random sentence 1
random sentence 2
random sentence 3
random sentence 4
random sentence 5

Again all within 1 cell. So how does one get what is highlighted in bold?
Thanks
 
Hi ,

If you know that there are 5 strings in the cell's text , the following formula can be used to return any particular string from all of the text which has been entered in cell A1.

=INDEX(TRIM(MID(SUBSTITUTE(A1,CHAR(10), REPT(" ", 100)),{1,101,201,301,401},100)),1)

returns the first string.

Changing the 1 to 2 , 3 , 4 , 5 will return the other strings in the text.

Narayan
 
Nifty trick.
wow it took 6 formulas to get it done and even then I don't get it but it work. LOL
This cant be well known and you must have come up with it?
Cool
 
Just for the sake of producing something entirely different (uses dynamic array functions)

64394
 

Attachments

  • Mary had a little lamb.xlsx
    16.6 KB · Views: 5
....the following formula can be used to return any particular string from all of the text which has been entered in cell A1.
=INDEX(TRIM(MID(SUBSTITUTE(A1,CHAR(10), REPT(" ", 100)),{1,101,201,301,401},100)),1)
returns the first string.
Changing the 1 to 2 , 3 , 4 , 5 will return the other strings in the text.
Narayan
1] For return single position string

The INDEX() is not required, it can simpler use by :

=TRIM(MID(SUBSTITUTE(CHAR(10)&A1,CHAR(10),REPT(" ",100)),1*100,100))

Whereas the 1 can change to 2,3,4......etc. in respect of position number of line you wanted

Example 1 :

To extract position number 4 line data

C2 : =TRIM(MID(SUBSTITUTE(CHAR(10)&A1,CHAR(10),REPT(" ",100)),B2*100,100))

64401

And,

2] For return multiply position string

you can adopt Excel 2013 new FILTERXML() for multiply selected number, e.g. 2 & 5 , or 2 to 4.... etc.

Example 2 :

To extract position number 2 and 5 line data

C2, copied across :

=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE($A2,CHAR(10),"</b><b>")&"</b></a>","//b[position() =2 or position() =5]["&COLUMN(A1)&"]"),"")

64402

Example 3 :

To extract position number 2 to 4 line data

C2, copied across :

=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE($A2,CHAR(10),"</b><b>")&"</b></a>","//b[position() >1 and position() <5]["&COLUMN(A1)&"]"),"")

64403

Example 4 :

To extract position number 1,3 &5 line data in combined result

C2, enter array (CSE) formula :

=FILTERXML("<a><b>"&SUBSTITUTE($A2,CHAR(10),"</b><b>")&"</b></a>","//b[position() mod 2=1]")

64427

Regards
Bosco
 
Last edited:
Because my solution was somewhat boring and pedantic (despite the non-standard presentation) I examined the more creative formulas offered by @NARAYANK991 and @bosco_yip . The first formula
= TRIM( MID( SUBSTITUTE( ¶ & text, ¶, REPT(" ", length)), instance*length, length ) )
didn't quite work for me so using "." in place of " " allowed be to determine why
64420
If the length=100 is too small the later lines of text are returned in the wrong row. Redefining length to refer to
= LEN(text)
sorted the problem.

Bosco's worked fine but I set out to build the formula step by step starting with
= "<text>"&¶&" <line>"&SUBSTITUTE(text,¶,"</line>"&¶&" <line>")&"</line>"&¶&"</text>"
which leave line feeds in the formula to format the XML serialisation for human redability.
64421
The worksheet formula
= FILTERXML(XMLserialised,"//line")
returned the lines as a dynamic array which suited me.

Thank you both!
 

Attachments

  • Mary had a little lamb.xlsx
    19.4 KB · Views: 5
Back
Top