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?
--
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
>
>

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
>
>

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
> >
> >

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
> > >
> > >

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
> > > >
> > > >

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
> >
> >

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
> > >
> > >

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" <(E-Mail Removed)> wrote in message
newsF834ADE-34B9-4087-A022-(E-Mail Removed)...
> 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
>> > > >
>> > > >

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
> > >
> > >