| Links | Alphabetical Index | Extracting particular words from text strings |

Excel function to split a string into shorter separate fixed length fields

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.

- A B C
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.

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

- C D E   Explanation
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 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
' nothing
For n = MyStrLen To 1 Step -1
 If Mid(TempStr, n, 1) = "-" Or Mid(TempStr, n, 1) = " " Then
  BreakPos = n
  Exit For
 End If
Next n
TempStr = Trim(Left(TempStr, BreakPos - 1))
End If

StPos = StPos + Len(TempStr) + 1
MakeStr = MakeStr & TempStr & Space(MyStrLen - Len(TempStr))

MakeStr = Trim(MakeStr)
End Function

excel Get this information as a document
accompanied by Excel worksheets
Document is in PDF format Click here for details about obtaining this
file. It has been rewritten for Excel 2010.

file: xl-Split-text.htm Page last updated: Feb14 2015 CMIDX S3 P7 Y