Help! Need to break down information

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tankgirl215 - 03 Jul 2006 16:25 GMT

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!

Reply to this Message

Scoops - 03 Jul 2006 17:24 GMT

> 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
[quoted text clipped - 5 lines]
> (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

Reply to this Message

tankgirl215 - 03 Jul 2006 18:21 GMT

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!!

Reply to this Message

Scoops - 03 Jul 2006 20:37 GMT

> Thanks Scoops!! That worked wonders!!
>
[quoted text clipped - 5 lines]
>
> 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

Reply to this Message

tankgirl215 - 03 Jul 2006 19:20 GMT

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!

Reply to this Message

Scoops - 03 Jul 2006 20:39 GMT

> 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

Reply to this Message

Rate this thread: