7

I need to take the first two lines of text from a wrapped cell in Excel. For example, a wrapped Excel cell contains the text as follows:

wrapedtext1

wrappedtext2

wrappedtext3

wrappedtext4

I need only the first two lines as 'wrapedtext1wrappedtext2'. Is it possible?

double-beep
  • 5,031
  • 17
  • 33
  • 41
divz
  • 7,847
  • 23
  • 55
  • 78
  • 1
    Have you tried grabbing the contents of that cell and seeing exactly what characters it contains - by printing out ASCII codes, for example, using the Asc() function? That may give you a clue as to how to get what you want. – JTeagle Mar 27 '12 at 11:56
  • 1
    @JTeagle : I also has this problem. After setting the wrapText property to the cell, the cell content is automatically wrapped with column width. Ie., we actually do not put line breaks. I am able to extract 2 lines when we enter data by pressing Alt + Enter. But here doing is that just setting the wrap text property to the cell – skmaran.nr.iras Mar 27 '12 at 12:04
  • 1
    @1355 - I understand. I fear you may have difficulty here since although you can probably get the width of the cell and even the font in use, VBA does not (to my knowledge) provide a way to 'measure' text as you can in C++ and possibly native VB. Can I ask why you need only the first two lines? Since the width of the cell (and thus, how much fits) can easily be changed at run time, it seems a slightly arbitrary thing to do? – JTeagle Mar 27 '12 at 12:35
  • 1
    What I need is to validate length of some strings (which has to be displayed in a device) through excel. I have set all the properties to the excel column. In device only two lines will be displayed. So I need to extract the string that fits in the device. – skmaran.nr.iras Mar 27 '12 at 12:45

5 Answers5

5

I only need to get first two lines as 'wrapedtext1wrappedtext2' .Is it possible???

Yes it might be possible but there is NO SIMPLE way to achieve it. There are lot of factors that you will have to consider.

1) Row Height in Pixels

2) Font Type and Size

3) Line Spacing

4) Is the Cell Merged?

5) Is the cell in Autofit state

6) Is all the text in Normal mode or does it have any Bold/Italics/Underline character(s) etc etc

Consider this snapshot

enter image description here

For example, Row Height in Pixels can be derived from

Debug.Print Range("A1").Height * (24 / 18)

Font Size in the above case can be achieved from this

Debug.Print Range("A1").Font.Size

But the challenge is what would happen in the below scenario?

enter image description here

In my opinion, it would be too much of a pain to achieve what you want. The best part would be to use ALT + Enter to insert line breaks and then retrieve the text.

FOLLOWUP

The strings are entered into the wrapped cell through vba code. So How will insert data by pressing alt + enter? – 1355 4 hours ago

In such a scenario, you can take a similar approach as well.

Sub Sample()
    Dim strg As String

    strg = "This is a sample" & vbCrLf & _
           "sentence which is" & vbCrLf & _
           "in Cell A1 and the" & vbCrLf & _
           "text is separated" & vbCrLf & _
           "with line breaks"

    With Range("A1")
        .Columns(1).ColumnWidth = 16.86
        .Font.Name = "Calibri"
        .Font.Size = 11
        .Value = strg
    End With
End Sub

NOTE: For the above you will have to record a macro and see what is the font, font size and column width that can take a particular formatting. Again, you will have to consider the fact that the example that I have given above is for a non formatted cell in a new sheet. If you are writing to a merged cell or a per-formatted cell then you will have to change the above code accordingly which can be easily achieved by recording a macro. I am also assuming that the ZOOM level is set to 100%

SNAPSHOTS

enter image description here

HTH

Sid

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • 1
    The strings are entered into the wrapped cell through vba code. So How will insert data by pressing alt + enter? – skmaran.nr.iras Mar 28 '12 at 03:43
  • 1
    Good Question. :) Probably find the cell width and then deciding on number of words that should go into each line? Let me see if I can show you an example :) – Siddharth Rout Mar 28 '12 at 08:06
  • Thanks for your response. Again a problem is there. I couldn't insert line breaks into the string manually. I need to check that how many words will be accommodated in two lines. – skmaran.nr.iras Mar 28 '12 at 11:16
  • 1
    Exactly my point. You need to first decide on which font/font size are you using as it will greatly depend on this. Like I mentioned, it is not a piece of cake :) – Siddharth Rout Mar 28 '12 at 11:24
1

Use the FIND function:

=LEFT(B2,(FIND(CHAR(10),B2)))

  • FIND searches for the line break character (CHAR(10)
  • LEFT gives you the first X characters in B2, up to the number where it finds the line break
user2628294
  • 33
  • 3
0

You can do some complicated formula in Excel to achieve this without VBA code.

Assuming the multi-line text is in A1. If you just want the first two lines, you can do this:

Get the first line:

=MID(A1,1,IF(2=LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1,LEN(A1),SEARCH("@",SUBSTITUTE(A1,CHAR(10),"@",2))))

Get the second line:

=MID(A1,SEARCH("@",SUBSTITUTE(A1,CHAR(10),"@",1))+1,IF(2=LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1,LEN(A1),SEARCH("@",SUBSTITUTE(A1,CHAR(10),"@",2)))-SEARCH("@",SUBSTITUTE(A1,CHAR(10),"@",1)))

Then simply use CONCATENATE() to combine those two.

Have a look at this site that allow you to specify which line to get: http://www.excelblog.ca/separating-lines-from-multi-line-excel-cell/

William Niu
  • 15,798
  • 7
  • 53
  • 93
0

one single formula without VBA

=LEFT(B2,FIND(CHAR(10),B2)+FIND(CHAR(10),SUBSTITUTE(B2,LEFT(B2,(FIND(CHAR(10),B2))),"")))

Sebastien Michea
  • 9
  • 1
0

Hi ok there are two ways how to do it with the VBA Code.

First way. If the Lines are Separated with a space

Dim avarSplit As Variant

'If separated with a space
avarSplit = Split(Worksheets(2).Range("A7").Value, " ")

second way if they are separated with a break.

Dim avarSplit2 As Variant

'separated with a break
avarSplit2 = Split(Worksheets(2).Range("A8").Value, Chr(10))

Then you get an array where all the lines are seperated and you can just read them out...

Moosli

Moosli
  • 3,140
  • 2
  • 19
  • 45
  • They have already told that they enter data through vba code. So line breaks will not be there. Space will not be a valid line separator here. – user1142922 Mar 29 '12 at 04:08