This VBA program creates a user defined function to split a string of text into shorter fields, making line breaks at appropriate spaces and avoiding splitting words.
|1||Once the balances were extrapolated the business had an impressive future.|
|2||Once the balances were extrapo||lated the business had an impr||essive future.|
|3||Once the balances were||extrapolated the business||had an impressive future.|
Row 2 shows the text from A1 split into three blocks of 30 characters each (using the MID() function). Words are broken up.
Row 3 shows the same text split into three blocks of up to 30 characters each but with natural breaks where words end.
A user defined function called MakeStr(MyText, MyStrLen) can been written using visual basic. The first parameter requires a cell reference containing the original text string. The second parameter is the maximum number of characters allowed in each line.
This function can be very useful for tasks such as converting text so that it will fit in database fields. For example a list of medical procedures may need to be imported into a program which has a number of fixed width description fields.
The function cannot return numerous results and therefore it produces a single string which includes additional spaces padding the ends of each line. It can therefore be broken up into regular sized blocks without corrupting words.
|4||Rigid Sigmoidoscopy Including Proctoscopy And Biopsy||[B4] =MakeStr(A4,30)|
|5||Diagnostic Oesophago-Gastro- Duodenoscopy (Ogd) Includes Forceps Biopsy Urease Test||converts the text and|
|6||Diagnostic Colonoscopy Includes Forceps Biopsy Of Colon And Ileum||inserts padding|
|4||Rigid Sigmoidoscopy||Including Proctoscopy And||Biopsy||[C4] =MID($B4,1,30)|
|5||Diagnostic Oesophago-Gastro-||Duodenoscopy (Ogd) Includes||Forceps Biopsy Urease Test||[D5] =MID($B5,31,30)|
|6||Diagnostic Colonoscopy||Includes Forceps Biopsy Of||Colon And Ileum||[E6] =MID($B6,61,30)|
Column A (not shown) contains the original long descriptions. Column B uses the VBA Function to create a new string suitable for splitting / parsing.
If this process is used to populate a fixed number of description fields (e.g. 3 lines) there may be instances where the text will not fit in the available space. It may be useful to add characters to the end of the 3rd column indicating that the text has been trimmed (e.g 'sentence...' or 'sentence*'). [E4] =IF(LEN(B4)>90, LEFT((TRIM(MID(B4, 61, 30))), 27) & "…", MID($B4, 61, 30))
Function MakeStr(MyText As String, MyStrLen As Integer) As String
' written by MeadInKent.co.uk 2011
Dim StPos As Integer, MyTextLen As Integer
Dim BreakPos As Integer, n As Integer, TempStr As String
StPos = 1
MyTextLen = Len(MyText)
Do While StPos < MyTextLen
TempStr = Trim(Mid(MyText, StPos, MyStrLen))
If Mid(MyText, StPos + MyStrLen, 1) = " " Or StPos + MyStrLen > MyTextLen Then
For n = MyStrLen To 1 Step -1
If Mid(TempStr, n, 1) = "-" Or Mid(TempStr, n, 1) = " " Then
BreakPos = n
TempStr = Trim(Left(TempStr, BreakPos - 1))
StPos = StPos + Len(TempStr) + 1
MakeStr = MakeStr & TempStr & Space(MyStrLen - Len(TempStr))
MakeStr = Trim(MakeStr)
|Get this information as a document
accompanied by Excel worksheets
|Click here for details about
file. It has been rewritten for Excel 2010.
|file: xl-Split-text.htm||Page last updated: Oct11||© MeadInKent.co.uk 2013||CMIDX S3 P7 Y|