Long text to break in parts, lines

**pmbhome** · 02-16-2021, 05:31 AM

I have a data with millions of rows with Long text. but to include them from our excel to the current software, the acceptance is any number of lines, but each line not to exceed 70 characters.

Pls provide any formula to convert a long line to paragraph in separate lines.

Example - take the above lines, i need the output as below:

I have a data with millions of rows with Long text.
but to include them from our excel to the current software, the acceptance
is any number of lines, but each line not to exceed 70 characters.

Pls advise

**Glenn Kennedy** · 02-16-2021, 05:39 AM

One way:

=MID($A1,1+70*(COLUMNS($B1:B1)-1),70)

copied across.

**pmbhome** · 02-16-2021, 05:50 AM

Thanks Glenn,

But
1. The full work is broken in column "them" the in B and m in C, cant we have check for full words, i mean if including "them exceeds 70" then we can cut the sentence before the word "them"

2. Also the final output i want in one cell, is it possible too.

Once again thanks, and awaiting your reply soonest.

Regards
Pankaj

**Glenn Kennedy** · 02-16-2021, 05:59 AM

You didn't mention EITHER of those requirements in your first post, did you?

I doubt if you will get a formula to do that. if you are still using Excel 2003 and 2007, I do not think you can use Power Query, so you will need VBA. Is that allowed for you? If so, I will move the thread.

**pmbhome** · 02-16-2021, 06:26 AM

Sorry for not posting in 1st post, i assumed you understood it as truncating after possible word or full stop at 70 of just before 70.

I am using Excel 2016.
I u need to move and u think can get solutions pls do move the thread,
Because i need the solution on this it is too irritating to check one by one. There has to be something in Excel.

Pankaj

**Glenn Kennedy** · 02-16-2021, 06:29 AM

Alas, my crystal ball does not tell me about things that are NOT present in a description. I will leave it here for a while, as PowerQuery might work for you. I'm not very familiar with it, but others are...

Please update the Excel version on your profile, which is very out of date.

**Joske920** · 02-16-2021, 09:25 AM

Here is a start

Please Login or Register  to view this content.

**ChemistB** · 02-16-2021, 11:38 AM

Playing with Formula solutions, how many characters are you expecting in your strings? Less than 254?

**pmbhome** · 02-19-2021, 10:33 AM

No the text is more than 500.

Originally Posted by ChemistB

Playing with Formula solutions, how many characters are you expecting in your strings? Less than 254?

**JeteMc** · 02-22-2021, 02:16 PM

Please let us know whether or not Joske920's code does what you want so that we don't spend time working on an issue that has already been resolved.

**pmbhome** · 06-16-2021, 04:57 AM

Sorry for late reply!

64 characters in one line and 1900 total in one cell, that what my system allows.

**JohnTopley** · 06-16-2021, 07:29 AM

Please Login or Register  to view this content.

Length is 64 as per your LAST post (not 70))

with data in A1 put this in B1

=Char64(A1)

Cell "formatted" as "Wrap text"

OR

Please Login or Register  to view this content.

run the above macro which "loop" through all cells in column A (A1 down)

**pmbhome** · 06-16-2021, 09:15 AM

I TRIED IN 1 SHEET, I HAVE ATTACHED SAME SAMPLE TEXT IN A1, could u pls update the formula in this sheet and revert.

**JohnTopley** · 06-16-2021, 11:55 AM

Your data has control characters CRLF) which the VBA (currently) does not handle. Are ALL the scripts in this format?

Please Login or Register  to view this content.

Updated code.

**pmbhome** · 06-16-2021, 12:27 PM

THATS Perfect andwhat i wanted excatly.

Thanks John Topley..

**pmbhome** · 06-16-2021, 12:31 PM

Originally Posted by pmbhome

thats perfect andwhat i wanted excatly.

Thanks john topley..

i tried to drag the formula in a1, a2,a3........that doesnt work
any additonal changes to be made somewhere ?

**JohnTopley** · 06-16-2021, 12:35 PM

=char64(A1)

and drag down works.

**pmbhome** · 06-16-2021, 12:38 PM

NO I TRIED,

attached ur file,it shows error !!

**JohnTopley** · 06-16-2021, 12:45 PM

It worked for me!

**pmbhome** · 06-16-2021, 01:35 PM

well i have multiple excel version, maybe that may be cause.

**JohnTopley** · 06-16-2021, 02:49 PM

No ... VBA is independent of Excel version so it should work: I have never any situation where VBA did not run on later versions (than my "old" version) of Excel.

What error(s) do you get?

**pmbhome** · 06-16-2021, 11:07 PM

When i drag, i get #Name? in the fields

**pmbhome** · 06-16-2021, 11:15 PM

Originally Posted by pmbhome

When i drag, i get #Name? in the fields

i GOT IT MACROS WHEN ENABLED ITS OK,
BUT I CHANGE THE FUNCTION NAME TO MY OWN (LT64), IT GIVES #REF! IN THE CELL

Also when i tried on actual file it adds a space before the start of each new lines which is not required. (attached sample)

I am not so good in excel but tried to make changes in the code but cannot rename it and remove spaces.

**JohnTopley** · 06-17-2021, 01:42 AM

Please Login or Register  to view this content.

I have renamed the macro (although this should make no difference) and added TRIM to remove leading blanks should they occur.

I cannot do anymore as it ALWAYS works when I use it so there is no logical reason for it not to work for you.

=LinesX64(A1)

**pmbhome** · 06-17-2021, 04:56 AM

Thanks that worked almost for me, only when i check some lines manually they are more than 64, not too much but 67 i saw.

I wonder why only those lines were not limited to 64, and rest all were done perfectly.

**JohnTopley** · 06-17-2021, 04:58 AM

Post example of lines > 64.

**pmbhome** · 06-21-2021, 04:56 AM

Hi
Attached sample of 1 record and below found one sentence with 67 after using the code.
pls advise.

**JohnTopley** · 06-21-2021, 03:24 PM

I can't find why the odd case occurs: I will see if the VBA can be done differently but handing control characters is the issue.

**pmbhome** · 06-21-2021, 11:43 PM

Yes.. Thanks.
I was also surprised, first thought maybe special characters was issue, but that too i checked by replacing.
but my 95 % work is done now.
Would be better if that odd part is removed too.

**JohnTopley** · 06-22-2021, 02:19 AM

You can always pass it over the the VBA experts (I'm not one!) but hopefully you can manage with a few minor manual interventions.

Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Re: Long text to break in parts, lines

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How do I break up a long text cell into shorter ones withoug spli.

[SOLVED] Break cell into multiple lines by line break

Bookmarks

Bookmarks

Posting Permissions