Probably Easy "Left Function"

[Parra]

I am inporting a fixed width report into Excel, but at times 2 columns overlap. In cell A1 I have the account # and in B2 I have the account Name.

I need two things.

1. What would be the function to delete the characters at end of the account number (a1), some accounts begin with numbers. There is a space from the end of the Acc # and Letter.

2. What if I wanted this character too be put in the begining of cell B1.

Thanks

[Yogi Anand]

On 2002-05-23 10:45, Parra wrote:
I am inporting a fixed width report into Excel, but at times 2 columns overlap. In cell A1 I have the account # and in B2 I have the account Name.

I need two things.

1. What would be the function to delete the characters at end of the account number (a1), some accounts begin with numbers. There is a space from the end of the Acc # and Letter.

2. What if I wanted this character too be put in the begining of cell B1.

Thanks

You could use the LEFT and RIGHT functions for this. See the worksheet simulation:

Microsoft Excel - Book1
B12		=
	A	B	C	D
1	AccountInfo123	:alert('=RIGHT(A1,3)')>123
2	:alert('=LEFT(A1,11)')>AccountInfo	AccountName
3
4
5
Sheet2

Click on a hyperlinked cell to see the underlying formula

[ This Message was edited by: Yogi Anand on 2002-05-23 10:55 ]

[Mark W.]

Do you have a problem with the fixed width report itself? Are the account numbers variable length and not padded with enough trailing spaces to insure that the name on each row starts in the same column position?

[ This Message was edited by: Mark W. on 2002-05-23 10:59 ]

[Parra]

In reply to Mark's question, that is correct, I don't have a problem with the rest of the report just this portion. Like you said it is not padded.

[Parra]

Yogi, what if the account number varies.

Ex. In cell A1

C002051 This is ok
Z9M05 This is ok
601954 M WRONG a letter is at the end
A0064901 This is ok
602003 H WRONG a letter is at the end

I hope this gives you a better picture, Thanks.

[Mark W.]

So, am I correct in saying that this report really isn't fixed length? And, if it isn't... is it possible that the report is space delimited?

[ This Message was edited by: Mark W. on 2002-05-23 11:45 ]

[Aladin Akyurek]

On 2002-05-23 11:19, Parra wrote:
Yogi, what if the account number varies.

Ex. In cell A1

C002051 This is ok
Z9M05 This is ok
601954 M WRONG a letter is at the end
A0064901 This is ok
602003 H WRONG a letter is at the end

I hope this gives you a better picture, Thanks.

It's of course better to eliminate the real cause.

If needed, insert a column next to A, apply Data|Text to Columns to A, and

in D1 enter: =B1&C1 and copy down.

Apply Edit|Copy, then Edit|Paste Special >Values.

Delete unwanted columns.