Paragraph alignment in Excel when pasting from other Apps.
 

Hi,

Let's say I have a para in the following FORMAT in Outlook or Word. I want
to keep a record of this in excel. When I copy , paste then in Excel the
indentations are not removed.

**Pasting from outlook as it is**
Tab Table 29-a - I see many numbers for the same attribute on different
tables. What is the correct number? For ex.
Table 29-a - ABC =76 and ABC =209
Table 27-12 - ABC =453
**Pasting ends**

I have used Trim function but the leading spaces before -- Table 29-A -- and
the leading spaces before Table 27-12 are not getting removed. I have to
manually get in to edit mode and remove the leading spaces. Is there any
other function which can align the above 3 rows in Excel

Please guide me

Thanks a lot,
Hari
India

If you select that column and do
Data|Text to columns
Fixed width
remove any lines that excel guessed at (and don't add any of your own!)
Then plop the data right back where you got it, then both leading and trailing
spaces will be removed.

You could also use a worksheet formula and a helper column of cells:

=trim(a1)

This will remove leading/trailing and multiple consecutive internal spaces.

Then copy|paste special|values (over the original column???) and delete the
helper column.

Hari Prasadh wrote:

Hi,

Let's say I have a para in the following FORMAT in Outlook or Word. I want
to keep a record of this in excel. When I copy , paste then in Excel the
indentations are not removed.

**Pasting from outlook as it is**
Tab Table 29-a - I see many numbers for the same attribute on different
tables. What is the correct number? For ex.
Table 29-a - ABC =76 and ABC =209
Table 27-12 - ABC =453
**Pasting ends**

I have used Trim function but the leading spaces before -- Table 29-A -- and
the leading spaces before Table 27-12 are not getting removed. I have to
manually get in to edit mode and remove the leading spaces. Is there any
other function which can align the above 3 rows in Excel

Please guide me

Thanks a lot,
Hari
India

--

Dave Peterson

Hi Dave,

Im not able to illustrate my problem properly because Outlook Express and
Google are displaying (automatic wrapping of text) my post original
containing 3 lines of --**Pasting from outlook as it is** --to 4 lines.

Actually Im aware of Data text to columns (both Fixed width and delimiter)
along with Trim, but in this case they wont work.

a) Text to columns - Fixed width - In this case within a single column ONLY
SOME ROWS have leading spaces while the rest dont. So, Text to Col wouldnt
work.

b) Trim - I have tried but it isnt working in this case.

To overcome the problem of wrapping, let me give a different/smaller
example.

**Pasting starts**
How are you
Iam fine.
**Pasting ends

Now copy the above 2 lines between pasting starts and pasting ends in to
Excel and try text to columns and trim. It doesnt work in Excel 2002/Win XP.

Thanks a lot,
Hari
India

"Dave Peterson" wrote in message
...
If you select that column and do
Data|Text to columns
Fixed width
remove any lines that excel guessed at (and don't add any of your own!)
Then plop the data right back where you got it, then both leading and
trailing
spaces will be removed.

You could also use a worksheet formula and a helper column of cells:

=trim(a1)

This will remove leading/trailing and multiple consecutive internal
spaces.

Then copy|paste special|values (over the original column???) and delete
the
helper column.

Hari Prasadh wrote:

Hi,

Let's say I have a para in the following FORMAT in Outlook or Word. I
want
to keep a record of this in excel. When I copy , paste then in Excel the
indentations are not removed.

**Pasting from outlook as it is**
Tab Table 29-a - I see many numbers for the same attribute on different
tables. What is the correct number? For ex.
Table 29-a - ABC =76 and ABC =209
Table 27-12 - ABC =453
**Pasting ends**

I have used Trim function but the leading spaces before -- Table 29-A --
and
the leading spaces before Table 27-12 are not getting removed. I have to
manually get in to edit mode and remove the leading spaces. Is there any
other function which can align the above 3 rows in Excel

Please guide me

Thanks a lot,
Hari
India

--

Dave Peterson

Say this is in A1
How are you
and this is in A2
_____Iam fine.
(where _ indicates a space character)

Then select all of column A and do that Data|text to columns.

If you choose Fixed width and remove all those lines, then you'll end up with:

In A1
How are you
In A2
Iam fine.

(It won't damage the cells that are perfect, but it will fix the imperfect
ones.)


The only other thing that I can think of that would cause this technique to fail
is if you had funny characters (instead of spaces) in the cells.

If you were copying from a web page (HTML), this is a common problem. If the
message in Outlook is also HTML and uses those non-breaking spaces, then maybe
David McRitchie's TrimAll routine would help.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

Hari Prasadh wrote:

Hi Dave,

Im not able to illustrate my problem properly because Outlook Express and
Google are displaying (automatic wrapping of text) my post original
containing 3 lines of --**Pasting from outlook as it is** --to 4 lines.

Actually Im aware of Data text to columns (both Fixed width and delimiter)
along with Trim, but in this case they wont work.

a) Text to columns - Fixed width - In this case within a single column ONLY
SOME ROWS have leading spaces while the rest dont. So, Text to Col wouldnt
work.

b) Trim - I have tried but it isnt working in this case.

To overcome the problem of wrapping, let me give a different/smaller
example.

**Pasting starts**
How are you
Iam fine.
**Pasting ends

Now copy the above 2 lines between pasting starts and pasting ends in to
Excel and try text to columns and trim. It doesnt work in Excel 2002/Win XP.

Thanks a lot,
Hari
India

"Dave Peterson" wrote in message
...
If you select that column and do
Data|Text to columns
Fixed width
remove any lines that excel guessed at (and don't add any of your own!)
Then plop the data right back where you got it, then both leading and
trailing
spaces will be removed.

You could also use a worksheet formula and a helper column of cells:

=trim(a1)

This will remove leading/trailing and multiple consecutive internal
spaces.

Then copy|paste special|values (over the original column???) and delete
the
helper column.

Hari Prasadh wrote:

Hi,

Let's say I have a para in the following FORMAT in Outlook or Word. I
want
to keep a record of this in excel. When I copy , paste then in Excel the
indentations are not removed.

**Pasting from outlook as it is**
Tab Table 29-a - I see many numbers for the same attribute on different
tables. What is the correct number? For ex.
Table 29-a - ABC =76 and ABC =209
Table 27-12 - ABC =453
**Pasting ends**

I have used Trim function but the leading spaces before -- Table 29-A --
and
the leading spaces before Table 27-12 are not getting removed. I have to
manually get in to edit mode and remove the leading spaces. Is there any
other function which can align the above 3 rows in Excel

Please guide me

Thanks a lot,
Hari
India

--

Dave Peterson

--

Dave Peterson

Hi Dave,

I tried a couple of times in the last 2 days but wasnt working out.
Then I tried it now and am able to get what you said. Thnx a Ton for your
help.
(Dont understand why it wasnt working out earlier)

Thanks a lot,
Hari
India


"Dave Peterson" wrote in message
...
Say this is in A1
How are you
and this is in A2
_____Iam fine.
(where _ indicates a space character)

Then select all of column A and do that Data|text to columns.

If you choose Fixed width and remove all those lines, then you'll end up
with:

In A1
How are you
In A2
Iam fine.

(It won't damage the cells that are perfect, but it will fix the imperfect
ones.)


The only other thing that I can think of that would cause this technique
to fail
is if you had funny characters (instead of spaces) in the cells.

If you were copying from a web page (HTML), this is a common problem. If
the
message in Outlook is also HTML and uses those non-breaking spaces, then
maybe
David McRitchie's TrimAll routine would help.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

Hari Prasadh wrote:

Hi Dave,

Im not able to illustrate my problem properly because Outlook Express and
Google are displaying (automatic wrapping of text) my post original
containing 3 lines of --**Pasting from outlook as it is** --to 4 lines.

Actually Im aware of Data text to columns (both Fixed width and
delimiter)
along with Trim, but in this case they wont work.

a) Text to columns - Fixed width - In this case within a single column
ONLY
SOME ROWS have leading spaces while the rest dont. So, Text to Col
wouldnt
work.

b) Trim - I have tried but it isnt working in this case.

To overcome the problem of wrapping, let me give a different/smaller
example.

**Pasting starts**
How are you
Iam fine.
**Pasting ends

Now copy the above 2 lines between pasting starts and pasting ends in to
Excel and try text to columns and trim. It doesnt work in Excel 2002/Win
XP.

Thanks a lot,
Hari
India

"Dave Peterson" wrote in message
...
If you select that column and do
Data|Text to columns
Fixed width
remove any lines that excel guessed at (and don't add any of your own!)
Then plop the data right back where you got it, then both leading and
trailing
spaces will be removed.

You could also use a worksheet formula and a helper column of cells:

=trim(a1)

This will remove leading/trailing and multiple consecutive internal
spaces.

Then copy|paste special|values (over the original column???) and delete
the
helper column.

Hari Prasadh wrote:

Hi,

Let's say I have a para in the following FORMAT in Outlook or Word. I
want
to keep a record of this in excel. When I copy , paste then in Excel
the
indentations are not removed.

**Pasting from outlook as it is**
Tab Table 29-a - I see many numbers for the same attribute on
different
tables. What is the correct number? For ex.
Table 29-a - ABC =76 and ABC =209
Table 27-12 - ABC =453
**Pasting ends**

I have used Trim function but the leading spaces before -- Table
29-A --
and
the leading spaces before Table 27-12 are not getting removed. I have
to
manually get in to edit mode and remove the leading spaces. Is there
any
other function which can align the above 3 rows in Excel

Please guide me

Thanks a lot,
Hari
India

--

Dave Peterson

--

Dave Peterson