Remove the first word of every sentence in a cell

[BFLO2000]

Hello,

For the sentence: "A map is a visual representation . The work of a map is to illustrate geography."

I know that the formula "=RIGHT(B2,LEN(B2)-FIND(" ",B2))" will remove the first letter in the string.

Leaving "map is a visual representation . The work of a map is to illustrate geography."

How would I remove the first word of each sentence?

The output would look like " map is a visual representation . work of a map is to illustrate geography."

Thanks

 

[Rick Rothstein]

Will your sentences ever have abbreviation in it (for example, Dr. or Mr. or Sq. Ft. and so on)?

 

[BFLO2000]

Yes, this is possible.

 

[sandy666]

with your example (which is not representative I think)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]raw[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]A map is a visual representation . The work of a map is to illustrate geography.[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]finish[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]map is a visual representation . work of a map is to illustrate geography.[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Split = Table.SplitColumn(Source, "raw", Splitter.SplitTextByEachDelimiter({". "}, QuoteStyle.Csv, false), {"raw.1", "raw.2"}),
    Extract1 = Table.TransformColumns(Split, {{"raw.1", each Text.AfterDelimiter(_, " "), type text}}),
    Extract2 = Table.TransformColumns(Extract1, {{"raw.2", each Text.AfterDelimiter(_, " "), type text}}),
    Merge = Table.CombineColumns(Extract2,{"raw.1", "raw.2"},Combiner.CombineTextByDelimiter(". ", QuoteStyle.None),"finish")
in
    Merge[/SIZE]

 

[kweaver]

Also, are there always only 2 sentences?

 

[Rick Rothstein]

Yes, this is possible.

Then unless you can provide a list of all possible abbreviations, there is no way to write a formula or VBA code that would be able to distinguish that a period belonging to an abbreviation is different from a period that ends a sentence. This would mean that the word appearing after an abbreviation's period would also be deleted along with the first word of sentences that followed a sentence-ending period.

 

[DanteAmor]

Try this udf () considering the abbreviations Rick mentions.
If you have more abbreviations in your sentences, you can add them in the line of the array within the macro.


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:525.62px;" /><col style="width:443.88px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >A map is a visual representation . The work of a map is to illustrate geography.</td><td >map is a visual representation. work of a map is to illustrate geography. </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >sentences with abbreviation in it (Dr. or Mr. or Sq. Ft. and so on. Yes, this is possible.</td><td >with abbreviation in it (Dr. or Mr. or Sq. Ft. and so on. this is possible. </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >One sentence. Two sentence with . Three sentece with 3 words.</td><td >sentence. sentence with. sentece with 3 words. </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >many sentence. With abbreviation Dr. Last sentence. A dot.</td><td >sentence. abbreviation Dr. Last sentence. dot. </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=RemoveFirstWord(B2)</td></tr></table></td></tr></table>


Put the following code in a module.
You can call the function as in the previous example.

Function RemoveFirstWord(w As String)
    Dim abb As Variant, a As Variant, i As Long, c As String, cad As String
    abb = Array("Dr.", "Mr.", "Sq.", "Ft.")     '[COLOR=#0000ff]add here abbreviation[/COLOR]
    For Each a In abb
        w = WorksheetFunction.Substitute(w, a, Left(a, Len(a) - 1) & "@#")
    Next
    a = Split(w, ".")
    For i = 0 To UBound(a)
        c = WorksheetFunction.Trim(a(i))
        c = Mid(c, InStr(1, c, " ") + 1)
        cad = cad & c & ". "
    Next
    cad = WorksheetFunction.Substitute(cad, "@#", ".")
    RemoveFirstWord = Left(cad, Len(cad) - 2)
End Function

 

[Rick Rothstein]

Try this udf () considering the abbreviations Rick mentions.
If you have more abbreviations in your sentences, you can add them in the line of the array

Put the following code in a module.
You can call the function as in the previous example.

Function RemoveFirstWord(w As String)
    Dim abb As Variant, a As Variant, i As Long, c As String, cad As String
    abb = Array("Dr.", "Mr.", "Sq.", "Ft.")     '[COLOR=#0000ff]add here abbreviation[/COLOR]
    For Each a In abb
        w = [B][COLOR="#FF0000"]WorksheetFunction.Substitute[/COLOR][/B](w, a, Left(a, Len(a) - 1) & "@#")
    Next
    [B][COLOR="#0000FF"]a = Split(w, ".")[/COLOR][/B]
    For i = 0 To UBound(a)
        c = WorksheetFunction.Trim(a(i))
        c = Mid(c, InStr(1, c, " ") + 1)
        cad = cad & c & ". "
    Next
    cad = WorksheetFunction.Substitute(cad, "@#", ".")
    RemoveFirstWord = Left(cad, Len(cad) - 2)
End Function

(Red) Why not use VB's built-in Replace function instead of WorksheetFunction.Substitute?

(Blue) This might be a stretch, but what about sentences ending with question marks or exclamation marks or sentences beginning with a phrase encased in parentheses (or other kinds of brackets)? However, assuming sentence-ending periods are the only thing we need to concern ourselves with, here is another (slightly more compact) way to write your function...

Function RemoveFirstWord(ByVal S As String) As String
  Dim X As Long, V As Variant, Arr As Variant
  For Each V In Array("Mr.", "Mrs.", "Ms.", "Dr.", "Sq.", "Ft.")
    S = Replace(S, V, V & "##")
  Next
  Arr = Split(S, ". ")
  For X = 0 To UBound(Arr)
    Arr(X) = Trim(Replace(Split(Arr(X), " ", 2)(1), ".##", "."))
  Next
  RemoveFirstWord = Join(Arr, ". ")
End Function

 

[DanteAmor]

(Red) Why not use VB's built-in Replace function instead of WorksheetFunction.Substitute?

Good idea.

(Blue) This might be a stretch, but what about sentences ending with question marks or exclamation marks or sentences beginning with a phrase encased in parentheses (or other kinds of brackets)? However, assuming sentence-ending periods are the only thing we need to concern ourselves with, here is another (slightly more compact) way to write your function...

There are many ways to end a sentence, hopefully the OP will tell us which ones exist in your prayers. For now we are dealing with the sentences finished in period.

 

[Rick Rothstein]

There are many ways to end a sentence, hopefully the OP will tell us which ones exist...

I did say that I was "stretching" things by asking about question marks, exclamation marks, etc.; but the OP does need to come back and tell us one way or the other whether his sentences always end with a period or not

For now we are dealing with the sentences finished in period.

Agreed... which is why I posted the code that I did.