从长段落中提取90个字符的句子文本(不打断单词)的Excel公式。

huangapple go评论88阅读模式
英文:

Excel formula to extract 90 character sentence text (without breaking word) from a long paragraph

问题

在B1单元格中,您可以使用以下Excel公式来提取最多90个字符的完整句子文本(不打断单词):

=LEFT(A1, SEARCH("|", SUBSTITUTE(A1, " ", "|", 90))-1)

在C1单元格中,您可以使用以下Excel公式来提取接下来的90个字符的完整句子文本(不打断单词):

=MID(A1, SEARCH("|", SUBSTITUTE(A1, " ", "|", 90))+1, SEARCH("|", SUBSTITUTE(A1, " ", "|", 180))-SEARCH("|", SUBSTITUTE(A1, " ", "|", 90))-1)

这些公式将在文本中找到第90个空格并以此分割文本,确保不会打断单词,并将结果放入B1和C1单元格中。

英文:

I have very long paragraphs in column A in excel. Example entry in cell A1 is mentioned below:

> "The Essential Accessory • Adds comfort and safety for
> passengers by offering back support and also helps carry luggage
> • Built for the long haul from high strength steel and
> heavy-duty mounting brackets • Uses stock mounting points for a
> no fabrication installation Includes: Sissy Bar, Hardware, Standard
> Freedom Sissy Bar Pad (Freedom pad matches Cobra's Freedom and
> Smoothee seats) Also Available (Sold Separately): 1. Jumbo Freedom
> Pad 2. Replacement Standard Freedom Pad 3. Fluted Backrest Insert 4.
> Swept Backrest Insert"

I would like to pull up to 90 character complete sentence text (without breaking word) in B1 cell and remaining 90 character complete sentence text (without breaking word) in C1 cell. I would like to utilize value from B1 and C1 into description lines of google text ads.

Is there anyone who can provide me excel formulas or VBA script who would pull that data? For some reason LEFT function with 90 character condition is pulling sentence, but the last word is not a complete word (its breaking).

I tried the below formula:

=LEFT(A1,90)

which give me this value: The Essential Accessory • Adds comfort and safety for passengers by offering back s

However, i exactly need: "The Essential Accessory • Adds comfort and safety for passengers by offering back" which is a complete sentence.

And in C1 I want this value: "support and also helps carry luggage • Built for the long haul from high strength"

答案1

得分: 2

=TRIM(INDEX(LET(a,LEFT(A1,SEQUENCE(25,,91,-1)),b, FILTER(a,RIGHT(a)=" "),b),1))

英文:

If you only want the FIRST line up to a maximum of 90 characters, and you have Microsoft 365, you can use this formula:

=TRIM(INDEX(LET(a,LEFT(A1,SEQUENCE(25,,91,-1)),b, FILTER(a,RIGHT(a)=" "),b),1))

从长段落中提取90个字符的句子文本(不打断单词)的Excel公式。

If you want to parse the entire text, here is a VBA macro that uses Regular Expressions to parse the data into appropriate row lengths.

As written, it writes the results below the selected cell(s) in separate cells, but you can change this for your requirements.

I have added a column showing the line lengths, but that is not part of the macro.

Option Explicit
Sub WordWrap()
'requires reference to Microsoft VBScript Regular Expressions 5.5
'Wraps at W characters, but will allow overflow if a word is longer than W
Dim RE As RegExp, MC As MatchCollection, M As Match
Dim str As String
Dim W As Long
Dim rSrc As Range, C As Range
Dim mBox As Long
Dim I As Long
'with offset as 1, split data will be below original data
'with offset = 0, split data will replace original data
Const lDestOffset As Long = 1

Set rSrc = Selection
    If rSrc.Rows.Count <> 1 Then
        MsgBox ("You may only select" & vbLf & " Data in One (1) Row")
        Exit Sub
    End If
Set RE = New RegExp
    RE.Global = True
W = InputBox("Maximum characters in a Line: ", , 72)
    If W < 1 Then W = 79
For Each C In rSrc
str = C.Value
'remove all line feeds and nbsp
    RE.Pattern = "[\xA0\r\n\s]+"
    str = RE.Replace(str, " ")
    RE.Pattern = "\S.{0," & W - 1 & "}(?=\s|$)|\S{" & W & ",}"
    Debug.Print RE.Pattern
If RE.Test(str) = True Then
    Set MC = RE.Execute(str)
'see if there is enough room
I = lDestOffset + 1
Do Until I > MC.Count + lDestOffset
    If Len(C(I, 1)) <> 0 Then
        mBox = MsgBox("Data in " & C(I, 1).Address & " will be erased if you contine", vbOKCancel)
        If mBox = vbCancel Then Exit Sub
    End If
I = I + 1
Loop

    I = lDestOffset
    For Each M In MC
        C.Offset(I, 0).Value = M
        I = I + 1
    Next M
End If
Next C
Set RE = Nothing
End Sub

从长段落中提取90个字符的句子文本(不打断单词)的Excel公式。

答案2

得分: 2

如果只是前90个字符,可以使用以下公式:

=TEXTBEFORE(LEFT(A1,91)," ",-1)

如果你想要将文本分成每个90个字符的整词句子,你可以尝试以下方法,适用于Office 365:

=LET(a,90,
DROP(REDUCE("",SEQUENCE(INT(LEN(A1)/a)+2),
     LAMBDA(x, y,
            LET(z,LEN(TEXTJOIN(" ",1,x)),
VSTACK(x,
       IF(LEN(z)=LEN(A1),
          "",
          TEXTBEFORE( LEFT(
                           SUBSTITUTE(A1&" ",                                
                                      IF(LEN(z)=1,
                                         "",
                                         TEXTJOIN(" ",1,x)&" "),
                                      ""),
                           a+1),
                      " ",
                      -1)))))),
     1))
英文:

If it's just the first 90 use

=TEXTBEFORE(LEFT(A1,91)," ",-1)

if you want the text divided into chops of 90 character sentences of whole words,you could try the following using Office 365:

=LET(a,90,
DROP(REDUCE("",SEQUENCE(INT(LEN(A1)/a)+2),
     LAMBDA(x, y,
            LET(z,LEN(TEXTJOIN(" ",1,x)),
VSTACK(x,
       IF(LEN(z)=LEN(A1),
          "",
          TEXTBEFORE( LEFT(
                           SUBSTITUTE(A1&" ",                                
                                      IF(LEN(z)=1,
                                         "",
                                         TEXTJOIN(" ",1,x)&" "),
                                      ""),
                           a+1),
                      " ",
                      -1)))))),
     1))

答案3

得分: 1

如果您使用Office 365,这个公式应该有效:

=LEFT(A1,MAX(LET(spacePos,IFERROR(UNIQUE(FIND(" ",A1,SEQUENCE(LEN(A1)))),-1),IF(spacePos<=90,spacePos,-1))))
英文:

If you are on Office 365, this formula should work:

=LEFT(A1,MAX(LET(spacePos,IFERROR(UNIQUE(FIND(&quot; &quot;,A1,SEQUENCE(LEN(A1)))),-1),IF(spacePos&lt;=90,spacePos,-1))))

答案4

得分: 0

此解决方案使用额外的单元格来存储每个“句子”中第一个字符的位置,根据您的示例:

在B1中,然后在Excel中复制到C1、D1等等:

=IFERROR(MID($A$1,B2,C2-B2),RIGHT($A$1,LEN($A$1)-B2+1))

帮助单元格B2 = 1

在B3中的帮助单元格,然后在Excel中复制到C3、D3等等:

=+FIND(" ",$A$1,90+B2)+1

结果在此处

英文:

This solution uses extra cells to store the position of the first character for each "sentence" according to your example:

In B1 and copy with excel to C1 D1 etc.:

=IFERROR(MID($A$1,B2,C2-B2),RIGHT($A$1,LEN($A$1)-B2+1))

Helping cell B2 = 1

Helping cell in B3 and copy with excel to C3 D3 etc.:

=+FIND(&quot; &quot;,$A$1,90+B2)+1

Result here

huangapple
  • 本文由 发表于 2023年8月9日 15:19:28
  • 转载请务必保留本文链接:https://go.coder-hub.com/76865456-2.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定