Split text cells in 70 character chunks in Excel

0 votes

The data is coming from a SQL Server database, and I have an Excel file with text for the key in one cell and the record's key in another.

Line feeds and blank rows are present in the text cell, and I must divide its content into as many 70-character rows as are required. I have to use the same key value and "line sequence" number for every row. I must preserve full words in the text and adhere to the white space and line breaks that were included in the original cell.

Below is an example of one of the cells (A1 is the key cell, and A2 is the text cell):

A1
ANUAL-LCD-FIX#0
A2
1-Limpieza general.
2-Revision de tornilleria en todo el equipo, reapretar de ser necesario.
3-Revision de pines, que no esten danados, reemplazar de ser necesario, (revisar con ingenieria).
4-Revision de la pantalla, que este funcional y que no este golpeada.

enter image description here

After splitting, this is what I need to get; please note that 3 columns (A, B and C) and 6 rows (1..6) would need to be created:

A1
ANUAL-LCD-FIX#0
B1
01
C1
1-Limpieza general.
A2
ANUAL-LCD-FIX#0
B2
02
C2
2-Revision de tornilleria en todo el equipo, reapretar de ser
A3
ANUAL-LCD-FIX#0
B3
03
C3
necesario.
A4
ANUAL-LCD-FIX#0
B4
04
C4
3-Revision de pines, que no esten danados, reemplazar de ser
A5
ANUAL-LCD-FIX#0
B5
05
C5
necesario, (revisar con ingenieria).
A6
ANUAL-LCD-FIX#0
B6
06
C6
4-Revision de la pantalla, que este funcional y que no este golpeada.

enter image description here

I have found some examples online that split cells, but the length of the cell being split is pre-determined, and they do not have white lines or line breaks; in my case, some of those cells are less than 70 characters, while others are much longer, so it is hard to know in advance how many rows will be needed to split each text cell.

Could anyone suggest to me how to accomplish this?

Oct 1, 2022 in Others by Kithuzzz
• 38,000 points
838 views

1 answer to this question.

0 votes

You can do this using Power Query

  • Data / Get& Transform / from Table/Range.
  • Split into rows on the linefeed character.
  • Add column using a custom function to split on maximum of 70th character preceding space.
  • Expand the splitted column.
answered Oct 2, 2022 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

Opening Selected Hyperlink DISPLAYING TEXT in Default Browser in MS Excel

Try this code: Sub Open_SelectedTextlinks() If Not ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,600 points
734 views
0 votes
1 answer

How to merge two cells in excel with same field name

Insert 2 new columns, G & H. Enter ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,600 points
1,620 views
0 votes
1 answer

In Excel, how to find a average from selected cells

If one has the dynamic array formula ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,600 points
766 views
0 votes
1 answer

Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 63,600 points
1,219 views
0 votes
1 answer

What is the character code for new line break in excel

Use CHAR(10) and Turn on Wrap Text option. It ...READ MORE

answered Dec 27, 2022 in Others by narikkadan
• 63,600 points
621 views
0 votes
1 answer

How to automate split by delimiter in Excel (equivalent of =SPLIT in gSheets)

Multiple ways, one is to use FILTERXML():   Formula in B2: =TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(A2," ...READ MORE

answered Dec 29, 2022 in Others by narikkadan
• 63,600 points
617 views
0 votes
1 answer
0 votes
1 answer

Highlighting Unique List of Words in Each Cell of a Selection of Cells - Excel VBA

In a Textbox it is a vbcrlf ...READ MORE

answered Jan 12, 2023 in Others by narikkadan
• 63,600 points
525 views
0 votes
1 answer
0 votes
1 answer

Excel, How to split cells by comma delimiter into new cells

The Excel manual method: choose Text to Column ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,600 points
916 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP