Consulting

Results 1 to 3 of 3

Thread: Add string of text to a specific point in every cells

  1. #1
    VBAX Regular
    Joined
    Mar 2018
    Posts
    17
    Location

    Add string of text to a specific point in every cells

    Hey everyone.

    Say I have a column A with 100 cells containing a paragraph of text, lets suppose something like this:

    Remember when you were young, you shone like the sun.
    Now there's a look in your eyes, like black holes in the sky.

    Say I want to ad in every cells a string of text, a variable that will change on every addition, between first and second sentences, like this:

    Remember when you were young, you shone like the sun.
    string
    Now there's a look in your eyes, like black holes in the sky.

    I suppose I have to search for "now there's" text and add the value of the string before it.
    How can I solve this with VBA?

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    re: between first and second sentences
    Remembering my primary school teacher who said something along the lines of A sentence starts with a capital letter and ends with a full stop (a period if you're in the States) then it might work if you look for the first full stop, and replace it with a full stop and the string you want to add. Your example shows a new line too, so I've thrown that in (vblf):
    Sub blah()
    myStringToAdd = "string"
    For Each cll In Range("A1:A100").Cells
      cll.Value = Replace(cll.Value, ".", "." & vbLf & myStringToAdd, , 1, vbTextCompare)
    Next cll
    End Sub
    or you could incorporate that new line within the string variable:
    Sub blah2()
    myStringToAdd = vbLf & "string"
    For Each cll In Range("A1:A100").Cells
      cll.Value = Replace(cll.Value, ".", "." & myStringToAdd, , 1, vbTextCompare)
    Next cll
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by kiltro View Post
    Say I have a column A with 100 cells containing a paragraph of text, lets suppose something like this:

    Remember when you were young, you shone like the sun.
    Now there's a look in your eyes, like black holes in the sky.

    Say I want to ad in every cells a string of text, a variable that will change on every addition, between first and second sentences, like this:

    Do you mean 'sentence' ( starts with a capital letter, and ends with a period, question mark, etc. )

    OR

    Just insert a new second line between the first and second?

    Looking at your example, it appears that a vbLF (char(10) is after the 'sun.'

    So you could have something like

    As I was walking<LF>
    down the street<LF>
    etc.

    If that's the case, you'd want to search for the first Chr(10) and insert 'string' after that giving

    As I was walking<LF>
    string<LF>
    down the street<LF>
    etc.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •