PDA

View Full Version : [SOLVED] Split some sentences by 'vbKeyEnter'



justuptou
03-03-2017, 10:23 PM
In Excel VBA, I have the data (some sentences) from userform / Winword, how can I split the sentences by 'vbKeyEnter' and put it into array (or Cells) if i have not specified the Punctuation.

For example :
I have two pencils, (1st sentence, comma + vbKeyEnter)
I put them to my bag (2nd sentence, only vbKeyEnter)
and I go home. (3rd sentence, fullstop + vbKeyEnter)

Target result :
Range (B1) = I have two pencils,
Range (C1) = I put them to my bag
Range (D1) = and I go home.

mana
03-04-2017, 03:33 AM
What do you mean?
1.vbKeyEnter
2.userform
3.Winword

mancubus
03-04-2017, 03:33 AM
try this:



Sub vbax_58775_split_cell_content_vbLf()

Dim arr

arr = Split(Range("A1").Value, vbLf)
Range("B1").Resize(, UBound(arr) + 1) = arr

End Sub

justuptou
03-04-2017, 04:53 AM
=Split(my sentences, "Enter")

what is the code or function to declare the"enter" ?
My original sentences are read from a table at winword file or textbox at excel userform



What do you mean?
1.vbKeyEnter
2.userform
3.Winword

mana
03-04-2017, 06:26 AM
??

Option Explicit


Sub test()
Dim wd As Object
Dim s As String
Dim a

Set wd = GetObject(, "word.application")
s = wd.activedocument.tables(1).cell(1, 1).Range.Text
a = Split(Left(s, Len(s) - 2), vbCr)
Range("b1").Resize(, UBound(a) + 1).Value = a

End Sub

justuptou
03-04-2017, 09:37 AM
The problem is solved, thank you.

Paul_Hossler
03-04-2017, 09:50 AM
You can use [Thread Tools] in the menu above your first post to mark this SOLVED