Calc: convert text to several columns

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Calc: convert text to several columns

mch-2
Dear all,
In calc (2.0), I'd like to rewrite the text of one column (i.e. "1.10 m")
into several different columns (i.e.    1  /  10  /m  ).
Is there a simple way to do it?
Thank you for your help

Marie Charlotte ANSTETT


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Calc: convert text to several columns

Anthony Chilco
Hi Marie,
If the text is in a consistent format, you could enter a formula into
each of the columns to parse the text, or use the text to columns macro
and use period and space as the delimiters.

For the sample text these formulae will work:

=mid(a1;1;find(".";a1)-1)
=mid(a1;find(".";a1)+1;find(" ";a1)-find(".";a1)-1)
=mid(a1;find(" ";a1)+1;99)

The macro would have to be downloaded and installed first.  You can
probably find it by searching the OOo web site.
tc


[hidden email] wrote:

>Dear all,
>In calc (2.0), I'd like to rewrite the text of one column (i.e. "1.10 m")
>into several different columns (i.e.    1  /  10  /m  ).
>Is there a simple way to do it?
>Thank you for your help
>
>Marie Charlotte ANSTETT
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: [hidden email]
>For additional commands, e-mail: [hidden email]
>
>
>  
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Calc: convert text to several columns

Terry North
In reply to this post by mch-2
I'd like to rewrite the text of one column ...into several different columns

You can use "paste special" with the option "transpose".  Regards.




---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Calc: convert text to several columns

Terry North
In reply to this post by mch-2
 <mch <at> pkg.fr> writes:
... to rewrite the text of one column (i.e. "1.10 m") into several different
columns (i.e.    1  /  10  /m  ).

I initially misunderstood.  You could use the functions LEFT, MID and RIGHT,
then copy the resulting range of cells containing the formulas, place the cell
pointer in the first cell and use "Paste Special", deselecting "Paste All" and
"Formats" and selecting the other categories.





---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Calc: convert text to several columns

Eike Rathke
In reply to this post by mch-2
Hi mch,

On Mon, Nov 21, 2005 at 18:15:29 +0100, [hidden email] wrote:

> In calc (2.0), I'd like to rewrite the text of one column (i.e. "1.10 m")
> into several different columns (i.e.    1  /  10  /m  ).

Maybe this Text to Columns macro is of help:
http://www.ooomacros.org/user.php#104183

  Eike

--
 OOo/SO Calc core developer. Number formatter bedevilled i18n transpositionizer.
 GnuPG key 0x293C05FD:  997A 4C60 CE41 0149 0DB3  9E96 2F1A D073 293C 05FD

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Re: Calc: convert text to several columns

Paul
In reply to this post by Terry North
> I'd like to rewrite the text of one column ...into several different columns
>
>> You can use "paste special" with the option "transpose".  Regards.

"Transpose
Cells from the left column become the top row and the top row becomes
the left column. Objects contained in the clipboard are scaled to fit
the output range. "

Don't think thats what the OP was asking for. I recommend the macro
add in option which can be found here :
http://ooomacros.org/user.php#104183

/paul


>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]