>Re : Excel Wrap-Text with Relatively Loose Strings
>
[quoted text clipped - 51 lines]
>
>8. Regards.
Fairly simple to do using regular expressions and a VBA Macro, given your data
format as presented.
1. Replace the existing white-space characters (spaces and line-feeds) with a
single space.
2. For every sequence of <space> followed by two or more capital letters, and
followed by another <space>, replace the initial <space> with a LF. This
format is critical, as it is the only thing in your data that seems to
differentiate the beginning of a new line.
3. (Not included in this macro, since more information would be needed), size
appropriately the rowheight and columnwidth.
Note: In this macro, the "wrapped" text is written into the adjacent cell. It
could just as easily replace the existing cell, but things are easier to debug
this way. You should be able to modify it to your needs.
Note2: I'm not sure, from your specifications, just what you want to do with
any extra <space>'s surrounding the slash. In this example, I trimmed this
also to a single space on either side. But this is easily modified.
In any event, put the code below into a regular module; select your cell(s)
with the data; and RUN the macro.
To enter the macro, <alt-F11> opens the VB Editor.
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.
To RUN the macro, first select your cell(s) with the data to be corrected.
<alt-F8> opens the macro dialog box. Select the Wrap macro, then RUN.
====================================
Option Explicit
Sub Wrap()
Dim c As Range
Dim re As Object
Dim str As String
Const sPat1 As String = "(\s+)"
Const sRep1 As String = " "
Const sPat2 As String = "\s([A-Z]{2,}\s)"
Const sRep2 As String = vbLf & "$1"
Set re = CreateObject("vbscript.regexp")
With re
.ignorecase = False
.Global = True
End With
For Each c In Selection
str = c.Value
re.Pattern = sPat1
If re.test(str) = True Then
'Replace all line feeds and extraneous spaces
'with a single space
str = re.Replace(str, sRep1)
'Add LF before each sequence of 2+ capital letters
'except the very first
re.Pattern = sPat2
str = re.Replace(str, sRep2)
c.Offset(0, 1).Value = str
End If
Next c
End Sub
======================================
--ron
TKT-Tang - 13 May 2008 00:52 GMT
Mr. Ron Rosenfeld,
Thank you for responding to the query case.
Will study your recommendation.
Wish to have a formula applied adjacent to the affected cell(s) such
that,
Copy > Paste Special will then overwrite the extraneous strings in the
affected cell(s).
As for, 'Add LF before each sequence of 2+ capital letters ......
the distinguishing feature being the lengthy spacing strings which
will be replaced by LF's respectively.
Thank you once again.
Ron Rosenfeld - 13 May 2008 01:44 GMT
>Mr. Ron Rosenfeld,
>
[quoted text clipped - 12 lines]
>
>Thank you once again.
You're welcome.
Post back after you have had a chance to try this out. I did not use a
formula, but could if necessary.
--ron