Split a Column into 2, use a character limit that does not cut words in half.

[vishesh10667]

Title says it all.

I have huge columns of data that can only be 30 characters long. I want to use a formula to split the column into two. The original column will have a character limit of 30, and should not cut words in half. If the Length will be more than 30 while including the entire last word of the cell, that word should be the first displayed in adjacent cell in the 2nd column.

The 2nd column should also be 30 characters long. And rest in 3 column


I usually use a Text to Columns method, but dont know how to make it not cut off the words. Any ideas?

Thanks in advance

 

[Rick Rothstein]

You asked two separate questions in Message #38... my response was for your first question (which I quoted in my response so you would know my code was for it). Are you saying you did not want that question answered separately? Or are you saying you did want it answered separately, but that my code is not doing exactly what you wanted?

As for your second question, I am not totally clear on it... can you post an example text and then show us what you want in each cell from it?

 

[Peter_SSs]

1. Can I give the code 2nd number of characters to split and 3rd number like "1st cell should be 30 characters (as its already in the code) 2nd cell should be only 20 cell and so on ..."

Try this modification to the post #6 code which I assume you were using before. You can specify how many characters per column in the 'Const' line near the start of the code by altering or adding to the numbers, leaving a space between each number.

If there are more characters in the cell than that 'Const' line allows for then all the remaining text is entered into the next column - see row 2 with my sample data and results below.

Sub BreakItUp_v2()
  Dim s As String
  Dim k As Long, tmp As Long
  Dim vCharsperCol As Variant, vResult As Variant
  Dim c As Range
 
  Const sCharsPerCol As String = "30 15 12"     '<-Change to suit
  
  vCharsperCol = Split(sCharsPerCol)
  For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
    s = c.Text
    k = 0
    ReDim vResult(0 To Len(s))
    Do Until Len(s) = 0 Or k = UBound(vCharsperCol) + 1
      tmp = vCharsperCol(k)
      vResult(k) = RTrim(Left(s, InStrRev(s & Space(tmp), " ", tmp + 1) - 1))
      s = Mid(s, Len(vResult(k)) + 2)
      vResult(k + 1) = s
      k = k + 1
    Loop
    If k > 0 Then c.Offset(, 1).Resize(, k + 1).Value = vResult
  Next c
End Sub

zvcknvrv 2020-06-08 1.xlsm
	A	B	C	D	E	F
1	The horse is one of two extant subspecies of Equus ferus	The horse is one of two extant	subspecies of	Equus ferus
2	This cell contains enough characters to exceed the column limits and then some more as well	This cell contains enough	characters to	exceed the	column limits and then some more as well
3	Short text	Short text
4
5	This should have a break after 30 characters	This should have a break after	30 characters
Sheet1

2. for paragraph question Im asking for another code : that search for a specific word and cut the text after it and past it in the next cell (ex: 5 Blog posts with 2 Paragraphs each and have got "HOW TO USE" heading for 2nd paragraph, so I want all 1st paragraphs in a column "including HOW TO USE heading" and 2nd paragraphs in the next columns.

Like Rick, I am also unsure of exactly what you have and what you want. Please post 5 or 5 samples and the expected results with XL2BB so that we can see exactly what you have, where as well as what you want and where and we can copy it for testing.

 

[zvcknvrv]

thank you for the code I really appreciate it. Here's the example I putted the full post in 1st column and what I want to do with it in the next 2 columns and I "HOW TO USE" in Red color is the constent that I wanna split the posts with like in this example (or maybe having option to change it depending on type of posts)

Book1
	A	B	C
2	1. Technique: Make sure all organic matter is removed from hands. All visible organic matter (for example: dirt) must be removed from hands prior to applying waterless hand sanitizer. Apply a dime sized amount of waterless hand sanitizer to the palm of one hand or use a waterless hand sanitizer wipe. Rub hands together covering all surfaces of hands and fingers. Rub until waterless hand sanitizer is absorbed. 2. How it works: Waterless hand sanitizer provides several advantages over hand washing with soap and water. However, they are not effective if organic matter (dirt, food, or other material) is visible on hands. Benefits of waterless hand sanitizer: require less time than hand washing act quickly to kill microorganisms on hands are more accessible than sinks reduce bacterial counts on hands do not promote antimicrobial resistance are less irritating to skin than soap and water some can even improve condition of skin	1. Technique: Make sure all organic matter is removed from hands. All visible organic matter (for example: dirt) must be removed from hands prior to applying waterless hand sanitizer. Apply a dime sized amount of waterless hand sanitizer to the palm of one hand or use a waterless hand sanitizer wipe. Rub hands together covering all surfaces of hands and fingers. Rub until waterless hand sanitizer is absorbed. 2.HOW TO USE :	Waterless hand sanitizer provides several advantages over hand washing with soap and water. However, they are not effective if organic matter (dirt, food, or other material) is visible on hands. Benefits of waterless hand sanitizer: require less time than hand washing act quickly to kill microorganisms on hands are more accessible than sinks reduce bacterial counts on hands do not promote antimicrobial resistance are less irritating to skin than soap and water some can even improve condition of skin
3	1. Benefits of homemade face masks: Cloth face masks can be made at home from common materials, so there’s an unlimited supply. They may lower the risk of people without symptoms transmitting the virus through speaking, coughing, or sneezing. They’re better than not using any mask and offer some protection, especially where physical distancing is hard to maintain. 2. HOW TO USE: Clean your hands, either by washing with soap and water or by using an alcohol-based hand sanitizer. Before putting the mask on, inspect it for any tears or holes. Locate the metal strip in the mask. This is the top of the mask. Orient the mask so that the colored side faces outward, or away from you. Place the top part of the mask on the bridge of your nose, molding the metal strip to the shape of your nose.	1. Benefits of homemade face masks: Cloth face masks can be made at home from common materials, so there’s an unlimited supply. They may lower the risk of people without symptoms transmitting the virus through speaking, coughing, or sneezing. They’re better than not using any mask and offer some protection, especially where physical distancing is hard to maintain. 2. HOW TO USE:	Clean your hands, either by washing with soap and water or by using an alcohol-based hand sanitizer. Before putting the mask on, inspect it for any tears or holes. Locate the metal strip in the mask. This is the top of the mask. Orient the mask so that the colored side faces outward, or away from you. Place the top part of the mask on the bridge of your nose, molding the metal strip to the shape of your nose.
Sheet1

 

[zvcknvrv]

 

[zvcknvrv]

Try this modification to the post #6 code which I assume you were using before. You can specify how many characters per column in the 'Const' line near the start of the code by altering or adding to the numbers, leaving a space between each number.

If there are more characters in the cell than that 'Const' line allows for then all the remaining text is entered into the next column - see row 2 with my sample data and results below.

Sub BreakItUp_v2()
  Dim s As String
  Dim k As Long, tmp As Long
  Dim vCharsperCol As Variant, vResult As Variant
  Dim c As Range

  Const sCharsPerCol As String = "30 15 12"     '<-Change to suit
 
  vCharsperCol = Split(sCharsPerCol)
  For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
    s = c.Text
    k = 0
    ReDim vResult(0 To Len(s))
    Do Until Len(s) = 0 Or k = UBound(vCharsperCol) + 1
      tmp = vCharsperCol(k)
      vResult(k) = RTrim(Left(s, InStrRev(s & Space(tmp), " ", tmp + 1) - 1))
      s = Mid(s, Len(vResult(k)) + 2)
      vResult(k + 1) = s
      k = k + 1
    Loop
    If k > 0 Then c.Offset(, 1).Resize(, k + 1).Value = vResult
  Next c
End Sub

zvcknvrv 2020-06-08 1.xlsm
	A	B	C	D	E	F
1	The horse is one of two extant subspecies of Equus ferus	The horse is one of two extant	subspecies of	Equus ferus
2	This cell contains enough characters to exceed the column limits and then some more as well	This cell contains enough	characters to	exceed the	column limits and then some more as well
3	Short text	Short text
4
5	This should have a break after 30 characters	This should have a break after	30 characters
Sheet1

Like Rick, I am also unsure of exactly what you have and what you want. Please post 5 or 5 samples and the expected results with XL2BB so that we can see exactly what you have, where as well as what you want and where and we can copy it for testing.

This question is for the vba code: I want the code to keep spliting the words until the words end "like in v1 of vba code" ex: i want to set up only the 3 columns as '30 15 12' and all the rest columns should be 10 !
ps: it's like merging the first vba code who got spliting till words end + the second vba code who got the specific 3 columns config !

 

[Peter_SSs]

"HOW TO USE" in Red color is the constent that I wanna split the posts with ..

A little confusing as your first example in cell A2 has "How it works" not "How to use" ???

Assuming it is actually "How to use" in each case then try this

Sub HOW_TO_USE()
  Const myDelimiter As String = "HOW TO USE:"
 
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Replace What:=Chr(10), Replacement:="###", LookAt:=xlPart
    .Replace What:=myDelimiter, Replacement:=myDelimiter & "|", LookAt:=xlPart, MatchCase:=False
    .TextToColumns Destination:=.Offset(, 1), DataType:=xlDelimited, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|"
    .Replace What:="|", Replacement:="", LookAt:=xlPart
    .Resize(, 3).Replace What:="###", Replacement:=Chr(10), LookAt:=xlPart
  End With
End Sub

 

[mboo86]

Welcome to the MrExcel board!

Try these formulas copied down.

Book1
	A	B	C	D
1	This cell contains enough characters to exceed the column limit	This cell contains enough	characters to exceed the	column limit
2	Short text	Short text
3	This should have a break after 30 characters	This should have a break after	30 characters
Split column

Cell Formulas
Range	Formula
B1:B3	B1	=IF(LEN(A1)<=30,A1,LEFT(A1,FIND("#",SUBSTITUTE(LEFT(A1,31)," ","#",LEN(LEFT(A1,31))-LEN(SUBSTITUTE(LEFT(A1,31)," ",""))))-1))
C1:C3	C1	=IF(LEN(A1)<=61,REPLACE(A1,1,LEN(B1)+1,""),LEFT(REPLACE(A1,1,LEN(B1)+1,""),FIND("#",SUBSTITUTE(LEFT(REPLACE(A1,1,LEN(B1)+1,""),31)," ","#",LEN(LEFT(REPLACE(A1,1,LEN(B1)+1,""),31))-LEN(SUBSTITUTE(LEFT(REPLACE(A1,1,LEN(B1)+1,""),31)," ",""))))-1))
D1:D3	D1	=TRIM(SUBSTITUTE(A1,TRIM(B1&" "&C1),""))

If you wanted a macro to break up the column A values then you could try this in a copy of your workbook.

Sub BreakItUp()
  Dim s As String
  Dim k As Long
  Dim result As Variant
  Dim c As Range

  Const CharsPerLine As Long = 30     '<-Change to suit

  For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
    s = c.Text
    If Len(s) > 0 Then
      k = 0
      ReDim result(1 To Len(s) / CharsPerLine + 1)
      Do Until Len(s) = 0
        k = k + 1
        result(k) = RTrim(Left(s, InStrRev(s & Space(CharsPerLine), " ", CharsPerLine + 1) - 1))
        s = Mid(s, Len(result(k)) + 2)
      Loop
      c.Offset(, 1).Resize(, k).Value = result
    End If
  Next c
End Sub

Hi Peter, I used your formulas and it worked for me but on some rows theres a weird thing happening. The thing that i needed was that i needed to change the character limit to 40 without splitting words.

the formula change for character limit to 40 is;
Column B: =IF(LEN(A2)<=40,A2,LEFT(A2,FIND("#",SUBSTITUTE(LEFT(A2,41)," ","#",LEN(LEFT(A2,41))-LEN(SUBSTITUTE(LEFT(A2,41)," ",""))))-1))
Column C: =IF(LEN(A2)<=81,REPLACE(A2,1,LEN(B2)+1,""),LEFT(REPLACE(A2,1,LEN(B2)+1,""),FIND("#",SUBSTITUTE(LEFT(REPLACE(A2,1,LEN(B2)+1,""),41)," ","#",LEN(LEFT(REPLACE(A2,1,LEN(B2)+1,""),41))-LEN(SUBSTITUTE(LEFT(REPLACE(A2,1,LEN(B2)+1,""),41)," ",""))))-1))

Column D: =TRIM(SUBSTITUTE(A2,TRIM(B2&" "&C2),""))

For the most part it works. If a certain address has lets say 80 characters column D will be empty. But on a lot of instances column D will repeat the entire address of column A(with the full address) Highlighted in red. Text in column A is formatted as general.Would appreciate if i could use formula method rather than VBA or macros

 

[V620898]

split formula is in attachment.

 

[mboo86]

split formula is in attachment.

Hi v62. tried copying the formula but it's coming up with an error

Column A1
=UPPER(TRIM(IFERROR(LEFT(LEFT(A2,50),FIND(CHAR(1),SUBSTITUTE(LEFT(A2,50),””,CHAR(1),LEN(LEFT(A2,50)-LEN(SUBSTITUTE(LEFT(A2,50),””,””,))-1))1),””)))

Not sure what i'm doing wrong

Can you assist to copy paste the formulas for me pls.

 

[Peter_SSs]

For the most part it works. If a certain address has lets say 80 characters column D will be empty. But on a lot of instances column D will repeat the entire address of column A(with the full address) Highlighted in red. Text in column A is formatted as general.Would appreciate if i could use formula method rather than VBA or macros

If you still need help, can you give the sample data with XL2BB so that I can be sure I am testing with exactly what you have?