Word contents to Excel with special characteristics

[Vignesh Veerasamy]

Dear Gurus,

I have a sample word file in which the contents are in paragraph format I need to copy the contents to Excel & bring it in a single line.

Thanks in advance.

I tried copy & paste & use the below formula but it shows FALSE statement.

Formula used : IF(A1=LEN(45),A1&A2&A3&A4&A5&A6&A7)

Post Script : The main word file contains more than 500 pages.

 

[Nebu]

Hi:

Instead of if (a1=len(45), you should use if(len(a1)=45. Having said that , I guess you need vba or more flexible formula if you want to combine the text as you want.

Thanks

 

[Chihiro]

@Vignesh

What version of Excel do you use? If Office 365 and latest update you can use CONCAT() function combined with Search(">",) to make it more dynamic.

CONCAT() behaves just like CONCATENATE but also accepts range.

 

[Vignesh Veerasamy]

Hi:

Instead of if (a1=len(45), you should use if(len(a1)=45. Having said that , I guess you need vba or more flexible formula if you want to combine the text as you want.

Thanks

Hi Nebu,
Thank you I tried out the formula as you mentioned one observations is that certain cell contains values less than or equal to 45 in such case what formula need to be used?

CONCAT is not working I am using MS Office 2016.

 

[Nebu]

Hi:

Well it depends on what you want to do with the ones having length of less than or equal to 45. You can include that logic in your present if formula.

Thanks

 

[Vignesh Veerasamy]

Hi:

Well it depends on what you want to do with the ones having length of less than or equal to 45. You can include that logic in your present if formula.

Thanks

Hi Nebu,

Pl tell me how to add the logic in the formula for the ones less than 45.

 

[Nebu]

Hi:

Something like

if(len(a1)>45,blah, if(len(a1)<=45,blah))

Thanks

 

[jindon]

VBA, if you like...

Sub test()
    Dim fn As String, txt As String, i As Long
    fn = Application.GetOpenFilename("Document,*.doc*")
    If fn = "Fasle" Then Exit Sub
    With CreateObject("Word.Application").Documents.Open(fn)
        txt = .Content.Text
        .Close
    End With
    With CreateObject("VBScript.RegExp")
        .Global = True: .MultiLine = True
        .Pattern = ">([^>]+\r)+(?=(>|$))"
        If .test(txt) Then
            For i = 0 To .Execute(txt).Count - 1
                Cells(i + 1, 1).Value = .Execute(txt)(i)
            Next
        End If
    End With
End Sub

 

[Vignesh Veerasamy]

View attachment 29468 View attachment 29468

VBA, if you like...

Sub test()
    Dim fn As String, txt As String, i As Long
    fn = Application.GetOpenFilename("Document,*.doc*")
    If fn = "Fasle" Then Exit Sub
    With CreateObject("Word.Application").Documents.Open(fn)
        txt = .Content.Text
        .Close
    End With
    With CreateObject("VBScript.RegExp")
        .Global = True: .MultiLine = True
        .Pattern = ">([^>]+\r)+(?=(>|$))"
        If .test(txt) Then
            For i = 0 To .Execute(txt).Count - 1
                Cells(i + 1, 1).Value = .Execute(txt)(i)
            Next
        End If
    End With
End Sub

Hi Jindon.
Thanks for your reply.
One correction my output should be like as shown in below screen shot. Can I get VBA for this ?

 

[jindon]

Then change to

Sub test()
    Dim fn As String, txt As String, m As Object, n As Long
    fn = Application.GetOpenFilename("Document,*.doc*")
    If fn = "False" Then Exit Sub
    With CreateObject("Word.Application").Documents.Open(fn)
        txt = .Content.Text
        .Close
    End With
    With CreateObject("VBScript.RegExp")
        .Global = True: .MultiLine = True
        .Pattern = ">([^>]+\r)+(?=(>|$))": n = 1
        For Each m In .Execute(txt)
            Cells(n, 1).Value = m.Value
            n = n + m.Length - Len(Replace(m.Value, vbCr, ""))
        Next
    End With
End Sub

Eidt : Fixed a typo.