英文:
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))
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
答案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(" ",A1,SEQUENCE(LEN(A1)))),-1),IF(spacePos<=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(" ",$A$1,90+B2)+1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论