VBA code for Text-To-Column delimited with a fixed width.

[Jeremy4110]

Hi, I receive product files from suppliers. The files have a “Description (Long Examples)” (Example 1), the description can be from 10 characters to over a 1000 characters. The system my company uses has a 60 character limit per field. I use “Text-To-Columns”, Delimited, and set the limits at 60, 120, 180, 240, etc. (Example 2). This solves the field limitation problem for my system; however the problem with this is that it will break in the middle of words. I have to manually go back and correct this by taking the first letter or first few letters from column one and move them to the second, and then do the same from second to the third column and so on. (Example 3). Is there a process with VBA that I can use to go from step 1 to step 3 that will make a break at a maximum of 60 characters if it is at the end of a word or back the character limit down to previous space between the last two words? Any help is greatly appreciated, thank you.

Example 1

Description (Long Examples)	Length
SPOT SHOT INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS SPOT SHOT INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS	115
SPOT SHOT INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS SHOT INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS	110
SHOT INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS	100
INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS	95
CARPET STAIN REMOVER & ODOR ELIMINATORS INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS	87
STAIN REMOVER & ODOR ELIMINATORS INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS	80
REMOVER & ODOR ELIMINATORS INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS SPOT SHOT INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS	132
AND ODOR ELIMINATORS INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS SPOT SHOT INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS	126
ELIMINATORS INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS SPOT SHOT INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS	117

<tbody>
</tbody>

Example 2

Description 1	Len 1	Description 2	Len 2	Description 3	Len 3
SPOT SHOT INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS SP	60	OT SHOT INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS	55		0
SPOT SHOT INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS SH	60	OT INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS	50		0
SHOT INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS INSTANT	60	CARPET STAIN REMOVER & ODOR ELIMINATORS	39		0
INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS INSTANT CARP	60	ET STAIN REMOVER & ODOR ELIMINATORS	35		0
CARPET STAIN REMOVER & ODOR ELIMINATORS INSTANT CARPET STAIN	60	REMOVER & ODOR ELIMINATORS	26		0
STAIN REMOVER & ODOR ELIMINATORS INSTANT CARPET STAIN REMOVE	60	R & ODOR ELIMINATORS	20		0
REMOVER & ODOR ELIMINATORS INSTANT CARPET STAIN REMOVER & OD	60	OR ELIMINATORS SPOT SHOT INSTANT CARPET STAIN REMOVER & ODOR	60	ELIMINATORS	11
AND ODOR ELIMINATORS INSTANT CARPET STAIN REMOVER & ODOR ELI	60	MINATORS SPOT SHOT INSTANT CARPET STAIN REMOVER & ODOR ELIMI	60	NATORS	6
ELIMINATORS INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS	59	SPOT SHOT INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS	57		0

<tbody>
</tbody>

Example 3

Description 1	Len 1	Description 2	Len 2	Description 3	Len 3
SPOT SHOT INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS	58	SPOT SHOT INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS	57		0
SPOT SHOT INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS	58	SPOT INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS	52		0
SHOT INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS INSTANT	60	CARPET STAIN REMOVER & ODOR ELIMINATORS	39		0
INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS INSTANT	55	CARPET STAIN REMOVER & ODOR ELIMINATORS	39		0
CARPET STAIN REMOVER & ODOR ELIMINATORS INSTANT CARPET STAIN	60	REMOVER & ODOR ELIMINATORS	26		0
STAIN REMOVER & ODOR ELIMINATORS INSTANT CARPET STAIN	54	REMOVER & ODOR ELIMINATORS	26		0
REMOVER & ODOR ELIMINATORS INSTANT CARPET STAIN REMOVER &	58	ODOR ELIMINATORS SPOT SHOT INSTANT CARPET STAIN REMOVER &	58	ODOR ELIMINATORS	16
AND ODOR ELIMINATORS INSTANT CARPET STAIN REMOVER & ODOR	57	ELIMINATORS SPOT SHOT INSTANT CARPET STAIN REMOVER & ODOR	58	ELIMINATORS	11
ELIMINATORS INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS	59	SPOT SHOT INSTANT CARPET STAIN REMOVER & ODOR ELIMINATORS	57		0

<tbody>
</tbody>

 

[Rick Rothstein]

Both macros are way above my current ability, but I am learning. I firmly believe that there is no better place to learn then from those who know more than you, and by all means you both know way more than I do. Having this forum has been a great benefit to me and many others. I hope you both know how much people like me appreciate the help.

I am always happy when I read that someone is trying to figure out how my code works... if you should become stumped by any part of it, feel free to reply to this thread asking for hints or explanations as to what is going on in it and I will be happy to try and explain what I can about it. Also, I know I speak for the other volunteers here when I say thank you for your kind comments... we always like to hear that our efforts are appreciated in some way.