| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert string
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert string
A tiny question:
What should the function do to words like "chronosynclastic" which exceed 13 character with spaces? -- Gary''s Student - gsnu200727 "Excel ESG" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert string
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.... "Excel ESG" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert string
vbCrLf gives extra unnecessary CR symbol which can be displayed as a square,
that is why nicer to use vbLf "Ben McBen" wrote: 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.... "Excel ESG" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert string
Dos that depend on font family?
"vbapro" wrote: vbCrLf gives extra unnecessary CR symbol which can be displayed as a square, that is why nicer to use vbLf "Ben McBen" wrote: 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.... "Excel ESG" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert string
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. "Ben McBen" wrote: Dos that depend on font family? "vbapro" wrote: vbCrLf gives extra unnecessary CR symbol which can be displayed as a square, that is why nicer to use vbLf "Ben McBen" wrote: 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.... "Excel ESG" wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert string
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 ? -- "Ben McBen" wrote: 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.... "Excel ESG" wrote: 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert string
try with change vbCrLf by vbLf
"Excel ESG" wrote: 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 ? -- "Ben McBen" wrote: 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.... "Excel ESG" wrote: 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert string
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) "vbapro" wrote in message ... 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. "Ben McBen" wrote: Dos that depend on font family? "vbapro" wrote: vbCrLf gives extra unnecessary CR symbol which can be displayed as a square, that is why nicer to use vbLf "Ben McBen" wrote: 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.... "Excel ESG" wrote: 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert string
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? "Excel ESG" wrote: 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 ? -- "Ben McBen" wrote: 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.... "Excel ESG" wrote: 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert string
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 -- Shared knowledge makes you smarter "vbapro" wrote: try with change vbCrLf by vbLf "Excel ESG" wrote: 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 ? -- "Ben McBen" wrote: 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.... "Excel ESG" wrote: 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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert string
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 -- Shared knowledge makes you smarter "Ben McBen" wrote: 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? "Excel ESG" wrote: 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 ? -- "Ben McBen" wrote: 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.... "Excel ESG" wrote: 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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert string
On Tue, 5 Jun 2007 01:57:00 -0700, Excel ESG
wrote: 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 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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert string
On Tue, 05 Jun 2007 09:06:03 -0400, Ron Rosenfeld
wrote: ================================= 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 ========================================= 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 |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert string
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. -- Regards, Tom Ogilvy "Ben McBen" wrote: Dos that depend on font family? "vbapro" wrote: vbCrLf gives extra unnecessary CR symbol which can be displayed as a square, that is why nicer to use vbLf "Ben McBen" wrote: 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.... "Excel ESG" wrote: 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 |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert string
On Tue, 05 Jun 2007 09:06:03 -0400, Ron Rosenfeld
wrote: 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 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 |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert string
On Tue, 05 Jun 2007 09:06:03 -0400, Ron Rosenfeld
wrote: And DON't forget to set the reference to Microsoft VBScript Regular Expressions 5.5 under Tools/References --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Convert to string help | Excel Discussion (Misc queries) | |||
| Convert a string to a date? | Excel Discussion (Misc queries) | |||
| Convert a string to number | Excel Programming | |||
| convert string to date | Excel Worksheet Functions | |||
| Function to convert string | Excel Programming | |||
Linear Mode
