Ron
Mon May 12 06:11:40 CDT 2008
On Mon, 12 May 2008 02:50:00 -0700 (PDT), TKT-Tang <tkt_tang@hotmail.com>
wrote:
>Re : Excel Wrap-Text with Relatively Loose Strings
>
>1. Enters an Excel worksheet (received from the wilderness).
>
>2. In a fixed-width cell, there is an entry such as the following
>string :-
>
>ZSL - Postion
>Indicator
>HS - Hand switch / Push
>Button
>SS - Selector
>switch
>DCS - Distributed Control System SDV
>- Shut down
>Valve BDV -
>Blow down Valve
>LCP - Local control
>Panel BSY -
>Solenoid Valve
>
>3. It was after discreetly manipulating the string (by adding
>extraneous spaces between the given items, as shown), it rendered the
>string to assemble as follows (See ?!) :-
>
>ZSL - Postion Indicator
>HS - Hand switch / Push Button
>SS - Selector switch
>DCS - Distributed Control System
>SDV - Shut down Valve
>BDV - Blow down Valve
>LCP - Local control Panel
>BSY - Solenoid Valve
>
>3. Obviously, legibility of the string is at the mercy of the Cell-
>width ; subsequent changes of the Cell-width will render the string
>gibberish ...... and there, another repeated attempt to edit the
>string by adding or deleting the superfluous spaces here and there to
>rectify the situation.
>
>4. One is indeed hapless to contemplate a correction by inserting Alt-
>Enter between the given items and eliminating the spacing strings in-
>situ.
>
>5. Is there not an applicable formula to neutralize the exemplary
>loose string such that the subsequent normalization of the string
>would be an orderly assembly as shown above ?
>
>6. Note that embedded in that item, Hand switch / Push Button, there
>are 2 spaces located on both sides of the slash(/). And there, the
>length of spacing strings would be 3 spaces and beyond.
>
>7. Please share your experiences and recommend a solution.
>
>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