Home	Excel	Access
\| 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 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


    ' nothing

    Else

    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))

Loop



MakeStr = Trim(MakeStr)


End Function

Get this information as a document
accompanied by Excel worksheets

Click here for details about obtaining this
file. It has been rewritten for Excel 2010.

file: xl-Split-text.htm

Page last updated: Feb14

CMIDX S3 P7 Y