Convert string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have a string wich I want to convert.
All spaces should be replaced by an underscore,
and an (alt-enter) should be inserted after an underscore so that the string
is wrapped to a max length of 13 characters

e.g "Thanks very much in advance" should result in

Thanks_very_(Alt-Enter)
much_in_(Alt-Enter)
advance

Someone told me it should be possible with an UDF,
but I have no experience with that
 
A tiny question:

What should the function do to words like "chronosynclastic" which exceed 13
character with spaces?
 
Something like:

Public Function ChopAndTrim(strInput As String, intChopLen As Integer) As
String

Dim strArr() As String
Dim strTmp As String
Dim strOut As String
Dim i As Long

strArr = Split(strInput, " ")
For i = LBound(strArr) To UBound(strArr)
If Len(strTmp) + Len(strArr(i)) > intChopLen Then
strOut = strOut & strTmp & vbCrLf
strTmp = strArr(i) & "_"
Else
strTmp = strTmp & strArr(i) & "_"
End If
If i = UBound(strArr) Then
strOut = strOut & strArr(i)
End If
Next i

ChopAndTrim = strOut

End Function

You will need to set formatting to wrap text....
 
vbCrLf gives extra unnecessary CR symbol which can be displayed as a square,
that is why nicer to use vbLf
 
It does, but many high-usage fonts display this square. And the meaning of
vbLf is “soft†manual line breaking, while vbCrLf means a new paragraph.
 
Ive tried the function but the result is 2 (Alt-Enter) in front of the text

Ive copy an pasted the function so I dindnt make a typing error,

What is wrong ?

--
 
try with change vbCrLf by vbLf

Excel ESG said:
Ive tried the function but the result is 2 (Alt-Enter) in front of the text

Ive copy an pasted the function so I dindnt make a typing error,

What is wrong ?
Click to expand...
 
even better to use vbNewLine, that works on the Mac too

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Not sure! Perhaps you found a bug in my code. What string are you trying to
parse? Did you set wrap text? I dont think the VbCrLf change will make much
difference (I think you would just see a graphics character at the end of
each line with certain fonts)

You are using this in a cell arent you?
 
It was not working quit well,
If the 2nd line was > 13 it was not added

I changed it a litle bit and now it is perfect !

Thanks for your time and help,

Public Function FuncText(strInput As String, intChopLen As Integer) As String

Dim strArr() As String
Dim strTmp As String
Dim strOut As String
Dim i As Long

strArr = Split(strInput, "_")
For i = LBound(strArr) To UBound(strArr)

If Len(strTmp) + Len(strArr(i)) > intChopLen Then
strOut = strOut & strTmp & vbLf
strTmp = strArr(i)
Else
strTmp = strTmp & strArr(i)
End If
If i = UBound(strArr) Then
strOut = strOut & strTmp
Else
strTmp = strTmp & "_"
End If
Next i

FuncText = Ucase(strOut)

End Function
 
The VbCrLf added 2 linefeeds, changed in in VbLf,
The 2nd Line was not taken over changed your code into

Public Function FuncText(strInput As String, intChopLen As Integer) As String

Dim strArr() As String
Dim strTmp As String
Dim strOut As String
Dim i As Long

strArr = Split(strInput, "_")
For i = LBound(strArr) To UBound(strArr)

If Len(strTmp) + Len(strArr(i)) > intChopLen Then
strOut = strOut & strTmp & vbLf
strTmp = strArr(i)
Else
strTmp = strTmp & strArr(i)
End If
If i = UBound(strArr) Then
strOut = strOut & strTmp
Else
strTmp = strTmp & "_"
End If
Next i

FuncText = UCase(strOut)

End Function


Now it is working perfectly

Thank's very much
 
Hello,

I have a string wich I want to convert.
All spaces should be replaced by an underscore,
and an (alt-enter) should be inserted after an underscore so that the string
is wrapped to a max length of 13 characters

e.g "Thanks very much in advance" should result in

Thanks_very_(Alt-Enter)
much_in_(Alt-Enter)
advance

Someone told me it should be possible with an UDF,
but I have no experience with that
Click to expand...

Here is a UDF that will do this. After entering it into a module, from the top
menu of the VBEditor, select Tools/References and set a reference to Microsoft
VBScript Regular Expressions 5.5 (You'll find it in a long list that opens
up).

As structured, it will NOT split at other than a <space>. So if a single word
is longer than 13 characters (with no spaces), it will not be split. I'm not
sure what you want to do in that circumstance.

=================================
Option Explicit

Function Split13(str As String) As String
Const sSpace As String = " "
Const sUnderscore As String = "_"
Dim Temp As String
Dim sPattern As String

Dim oRegex As RegExp
Set oRegex = New RegExp
With oRegex
.Global = True
.IgnoreCase = True
.MultiLine = False
End With

sPattern = sSpace
oRegex.Pattern = sPattern

Temp = oRegex.Replace(str, sUnderscore)

sPattern = "([\w]{1,12}_)"
oRegex.Pattern = sPattern

Split13 = oRegex.Replace(Temp, "$1" & vbLf)

End Function
=========================================
--ron
 
=================================
Option Explicit

Function Split13(str As String) As String
Const sSpace As String = " "
Const sUnderscore As String = "_"
Dim Temp As String
Dim sPattern As String

Dim oRegex As RegExp
Set oRegex = New RegExp
With oRegex
.Global = True
.IgnoreCase = True
.MultiLine = False
End With

sPattern = sSpace
oRegex.Pattern = sPattern

Temp = oRegex.Replace(str, sUnderscore)

sPattern = "([\w]{1,12}_)"
oRegex.Pattern = sPattern

Split13 = oRegex.Replace(Temp, "$1" & vbLf)

End Function
=========================================
Click to expand...

The brackets are redundant in one of the sPatterns:

=================================================
Function Split13(str As String) As String
Const sSpace As String = " "
Const sUnderscore As String = "_"
Dim Temp As String
Dim sPattern As String

Dim oRegex As RegExp
Set oRegex = New RegExp
With oRegex
.Global = True
.IgnoreCase = True
.MultiLine = False
End With

sPattern = sSpace
oRegex.Pattern = sPattern

Temp = oRegex.Replace(str, sUnderscore)

sPattern = "(\w{1,12}_)"
oRegex.Pattern = sPattern

Split13 = oRegex.Replace(Temp, "$1" & vbLf)

End Function
===================================
--ron
 
The first 27 or so characters are considered control characters (lf and cr
are characters 10 and 13 respectively). Generally fonts don't produce a
displayable character for these. Excel pretty consistently displays the
Chr(13) as an excess character when wrap text is applied. For example, the
Character Map applet doesn't display characters before 33 (32 is a space) for
any font.

So using VbLF is an excellent suggestion. VBNewline produces the same as
vbCrLf in windows.
 
Option Explicit

Function Split13(str As String) As String
Const sSpace As String = " "
Const sUnderscore As String = "_"
Dim Temp As String
Dim sPattern As String

Dim oRegex As RegExp
Set oRegex = New RegExp
With oRegex
.Global = True
.IgnoreCase = True
.MultiLine = False
End With

sPattern = sSpace
oRegex.Pattern = sPattern

Temp = oRegex.Replace(str, sUnderscore)

sPattern = "([\w]{1,12}_)"
oRegex.Pattern = sPattern

Split13 = oRegex.Replace(Temp, "$1" & vbLf)

End Function
Click to expand...

And another variation which both takes care of an infrequent problem, and also
indicates a sPattern to use if you prefer to have words greater than 13 letters
long split so as not have any lines longer than 13.

You should use this UDF with the appropriate sPattern for your requirements.

=========================================
Option Explicit

Function Split13(str As String) As String
Const sSpace As String = " "
Const sUnderscore As String = "_"
Dim Temp As String
Dim sPattern As String
Dim oRegex As RegExp
Set oRegex = New RegExp
With oRegex
.Global = True
.IgnoreCase = True
.MultiLine = False
End With

sPattern = sSpace
oRegex.Pattern = sPattern

Temp = oRegex.Replace(str, sUnderscore)

sPattern = "(\w{1,12}(_|$))"

'Use this sPattern if you want to just split
'words with more than 13 characters

' sPattern = "((\w{1,12}(_|$))|(\w{12}[^_]))"

oRegex.Pattern = sPattern
Split13 = oRegex.Replace(Temp, "$1" & vbLf)

'strip off last <alt-enter>
sPattern = "\n$"
oRegex.Pattern = sPattern
Split13 = oRegex.Replace(Split13, "")

End Function
=================================
--ron
 
And DON't forget to set the reference to Microsoft VBScript Regular Expressions
5.5 under Tools/References
--ron
 
Back
Top