Trim column lengths for every column

[jsharpe]

HI All,

below is an example of the workbooks im working on. you will see in row 4 that it has the max length of each column. is there a way to trim the length of each column easier than doing 1 column at a time?

PurchaseOrderLine.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Import Set No	Line Number	Item ID	Unit of Measure	Unit Quantity	Pricing Unit	Unit Price Display	Required Date	Date Due	Extended Description	Supplier Ship Date	Revision Level
2	Alphanumeric	Decimal	Alphanumeric	Alphanumeric	Decimal	Alphanumeric	Decimal	Date	Date	Alphanumeric	Date	Alphanumeric
3	Required	Not Required	Required	Required	Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required
4	8	19	40	8	4	8	6	Date	Date	255	Date	20
5	S1	3	DRILL381	Each	6	Each	5.75	10/10/19	10/10/19	3/8 Inch 12V Cordless Drill, Keyless Chuck, Case.	10/10/19	1A
Sheet1

 

[jolivanes]

Some should be able to help if they know:

"max length of each column" Means what?

"trim the length" Means what?

What do 8, 19, 40, 8, 4, 8, 6, Date, Date etc. in Row 4 signify?

How does "Date" relate to max length?

 

[Asbestos_Jen]

The LEFT function will give you the leftmost n number of characters. If you wanted to return the trimmed cells in another cell the format would be as in row 8 below. There's probably a way to do it with VBA if you wanted to do it within the existing cells (but I am very novice with VBA, so someone else would have to help you there).

2024-06-19_2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Import Set No	Line Number	Item ID	Unit of Measure	Unit Quantity	Pricing Unit	Unit Price Display	Required Date	Date Due	Extended Description	Supplier Ship Date	Revision Level
2	Alphanumeric	Decimal	Alphanumeric	Alphanumeric	Decimal	Alphanumeric	Decimal	Date	Date	Alphanumeric	Date	Alphanumeric
3	Required	Not Required	Required	Required	Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required
4	8	19	40	8	4	8	6	Date	Date	255	Date	20
5	S1	3	DRILL381	Each	6	Each	5.75	43748	43748	3/8 Inch 12V Cordless Drill, Keyless Chuck, Case.	43748	1A
6
7
8	S1	3	DRILL381	Each	6	Each	5.75	43748	43748	3/8 Inch 12V Cordless Drill, Keyless Chuck, Case.	43748	1A
Sheet2

Cell Formulas
Range	Formula
A8:L8	A8	=IF(ISNUMBER(A4),LEFT(A5,A4),A5)

 

[jsharpe]

Hi,
the max length means the max character count in the cell.

"trim the length" Means what? shorten the character count to the max length allowed for the import.

What do 8, 19, 40, 8, 4, 8, 6, Date, Date etc. in Row 4 signify? these are the max lengths for each column.

How does "Date" relate to max length? The Date does not relate to the max length, this column will need to be ignored as it just the standard short date needed in the column.

Thanks
Josh

 

[Akuini]

Could you provide a more representative example along with the expected result?