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 in Others by Kithuzzz
• 20,660 points
67 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 by narikkadan
• 37,660 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 in Others by narikkadan
• 37,660 points
72 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 in Others by narikkadan
• 37,660 points
61 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 in Others by narikkadan
• 37,660 points
67 views
0 votes
1 answer
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
454 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,100 points
405 views
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 58,100 points
3,384 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
2,849 views
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 in Others by narikkadan
• 37,660 points
70 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