Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / May 2008

Tip: Looking for answers? Try searching our database.

Re : Excel Wrap-Text with Relatively Loose Strings

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
TKT-Tang - 12 May 2008 10:50 GMT
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.
Ron Rosenfeld - 12 May 2008 12:11 GMT
>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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.