• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 521
  • Last Modified:

Quick Access/VBA string function needed

Dear experts -
Hopefully a quick one - I need a function that will give me a maximum of x characters of a string fed to it, BUT not in the middle of a word (defined as ending in a space or hyphen) UNLESS there is only a single word there.
Can anyone solve this little riddle?
1 Solution
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Your going to need to write this if I'm understanding correctly.

Some examples would be good before we continue just to make sure were on the same page.

Dale FyeCommented:
So, if X characters ends in the middle of a word, what do you want, everything to the left of that word, or everything including that word?
The riddle is in interpreting your question.
Does this do it?
Function GetString(strText As String, iCharCount As Integer) As String
    Dim p As Integer
    strText = Replace(strText, "-", " ")
    p = iCharCount
    Do While Mid(strText, p, 1) <> " "
        If p = 1 Then
            GetString = Left(strText, iCharCount)
            Exit Function
            p = p - 1
        End If
    GetString = Left(strText, p)
End Function

Open in new window

terpsichoreAuthor Commented:
perfect. thanks so much.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now