Sep Data

[mtann]

ANY HELP OR IDEAS?

I have a sheet with 70 char's in Colum A

I need to put the first 30 char's in A, next 10 in B, and all remaining in C

Thanks
Matt

 

[Qroozn]

are you looking for a macro to do it... or something simple like copy/paste?

 

[mtann]

I would love to do a formula

concatenate, but the reverse

 

[Yogi Anand]

On 2002-05-08 18:45, mtann wrote:
ANY HELP OR IDEAS?

I have a sheet with 70 char's in Colum A

I need to put the first 30 char's in A, next 10 in B, and all remaining in C

Thanks
Matt

You can do it with DATA|Text_to_columns.

Hoever if you must do it with a formula --
if your entry is in cell A1, then

in A2 ... =left(a1,30)
in B2 ... =mid(a1,31,10)
in C2 ... =right(a1,30)

Regards!

NB: After Chris' sharp eyes caught it, I corrected B2 to have 10 as the last argument (instead of 40).
_________________
Yogi Anand

Edit: Deleted inactive website from hardcoded signature
This message was edited by Yogi Anand on 2003-01-19 13:17

 

[Chris Davison]

On 2002-05-08 20:03, Yogi Anand wrote:

On 2002-05-08 18:45, mtann wrote:
ANY HELP OR IDEAS?

I have a sheet with 70 char's in Colum A

I need to put the first 30 char's in A, next 10 in B, and all remaining in C

Thanks
Matt

You can do it with DATA|Text_to_columns.

Hoever if you must do it with a formula --
if your entry is in cell A1, then

in A2 ... =left(a1,30)
in B2 ... =mid(a1,31,40)
in C2 ... =right(a1,30)

Regards!

Yogi, just 10 needed in that 2nd part

so : in B2 ... =mid(a1,31,10)

 

[Yogi Anand]

Yogi, just 10 needed in that 2nd part

so : in B2 ... =mid(a1,31,10)



Hi Chris:
10 in the last spot would in this case definitely work, but in most cases we would rather put a larger number, sometimes as much as 255, as cleanup all the way to the right end of the string.

Edit: Chris, my apologies, I goofed, cell B1 needs only 10 characters -- 10 characters have nothing to do with the last split in cell of column C

Regards!
This message was edited by Yogi Anand on 2002-05-12 11:42

 

[Chris Davison]

Yogi,

you mean in most cases where there are only 2 columns and the rest are in column 2 ?

yours will never work in most cases of 3 columns !

I see what you're thinking, just didn't want to confuse the person asking the question which relates to 3 columns spcifically

Chris

 

[Yogi Anand]

On 2002-05-11 03:27, Chris Davison wrote:
Yogi,

you mean in most cases where there are only 2 columns and the rest are in column 2 ?

yours will never work in most cases of 3 columns !

I see what you're thinking, just didn't want to confuse the person asking the question which relates to 3 columns spcifically

Chris

Hi Chris:
My apologies -- Yes 30,10, and I meant the last 30 could be 30 , bigger or last bigger when I said 255, I was referring to the third column for picking up the right most values. Thanks for keeping me honest.

Regards!
This message was edited by Yogi Anand on 2002-05-12 11:33