Help! Need to break down information

[tankgirl215]

Hello All -

I'm building a product information database at work and have come
across a snag.

Our product titles are simply a sentence of sorts, but in the new db
they need to be broken up into three fields rather than one. Each
field also has a character limitation. Is there a way I can take the
product title from one sheet in the workbook and have it broken into
three pieces (obeying the character limitations of each field) on
another sheet?

(Including spaces- the first field has a 20 character limitation, as
does the second, and the third is only 10 characters.)

Thanks!

 

[Scoops]

Our product titles are simply a sentence of sorts, but in the new db
they need to be broken up into three fields rather than one. Each
field also has a character limitation. Is there a way I can take the
product title from one sheet in the workbook and have it broken into
three pieces (obeying the character limitations of each field) on
another sheet?

(Including spaces- the first field has a 20 character limitation, as
does the second, and the third is only 10 characters.)

Hi tankgirl125

Try these in three different cells in a row:

=LEFT(Sheet1!A1,20)
=IF(LEN(Sheet1!A1)<=20,"",MID(Sheet1!A1,21,20))
=IF(LEN(Sheet1!A1)<=40,"",MID(Sheet1!A1,41,10))

Amend the sheet/cell reference as necessary and copy down as far as
required.

Regards

Steve

 

[tankgirl215]

Thanks Scoops!! That worked wonders!!

Do you know if there's a way to lock columns in excel or entire sheets
so they can't be edited... I have to pass this sheet on to our buyers
to fill in additional information and would like it if they could only
edit the fields they need to. This workbook is 11,000 rows long and
100 columns wide - lots of room for error and stray typing I'm afraid.

Thanks!!

 

[tankgirl215]

Also - the end goal is to turn this file into an ascii file for upload
into our new system...is this going to take the forumlas I just pasted
in or the actual description information the formula displays?

Thanks!

 

[Scoops]

Thanks Scoops!! That worked wonders!!

Do you know if there's a way to lock columns in excel or entire sheets
so they can't be edited... I have to pass this sheet on to our buyers
to fill in additional information and would like it if they could only
edit the fields they need to. This workbook is 11,000 rows long and
100 columns wide - lots of room for error and stray typing I'm afraid.

Thanks!!

Hi tankgirl215

You can lock/unlock any cell you like.

By default, all cells are locked so that selecting Tools > Protection >
Protect Sheet prevents any cell from being edited (you can add a
password while you're there too).

To unlock a cell or range, select the range, click Format > Cells >
Properties and uncheck Locked > OK. Then protect the sheet as above.

I'm not sure that you're after total security but, for future
reference, be aware that Excel protection is not very strong and can be
broken relatively easily.

Apologies if any menus are mispelt I'm at home without my trusty Excel
to hand.

Regards

Steve

 

[Scoops]

Also - the end goal is to turn this file into an ascii file for upload
into our new system...is this going to take the forumlas I just pasted
in or the actual description information the formula displays?

Thanks!

Hi tankgirl215

I've never saved a file to ascii but I have to csv and that just takes
the data, not the formulae. I can only assume (which can be a
dangerous thing) that the same would be true of ascii also.

Regards

Steve