Help with formula
 

I've imported a text file and I'm testing some of the fields, like:

If(Left(B2,3)="A/R","",B2)

One of the cells in column B contains - in the 1st position the square
Box
Character, then 1234, sorta like x1234 (where x is the square box
character)

How can I incorporate this character into my above formula?

Tks in advance...

Help with formula
 

Do you know what that box character is?

If no:
=if(mid(b2,3)="123", ....

If yes, include it in your test.

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm

=if(left(b2,4)=char(10)&"a/r", ....

replace char(10) with the code you find using Chip's addin.

Jim May wrote:

I've imported a text file and I'm testing some of the fields, like:

If(Left(B2,3)="A/R","",B2)

One of the cells in column B contains - in the 1st position the square
Box
Character, then 1234, sorta like x1234 (where x is the square box
character)

How can I incorporate this character into my above formula?

Tks in advance...

--

Dave Peterson

Help with formula
 

If no:
=if(mid(b2,2,3)="123", ....

Oopsie!

Dave Peterson wrote:

Do you know what that box character is?

If no:
=if(mid(b2,3)="123", ....

If yes, include it in your test.

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm

=if(left(b2,4)=char(10)&"a/r", ....

replace char(10) with the code you find using Chip's addin.

Jim May wrote:

I've imported a text file and I'm testing some of the fields, like:

If(Left(B2,3)="A/R","",B2)

One of the cells in column B contains - in the 1st position the square
Box
Character, then 1234, sorta like x1234 (where x is the square box
character)

How can I incorporate this character into my above formula?

Tks in advance...

--

Dave Peterson

--

Dave Peterson

Help with formula
 

Jim May wrote:
I've imported a text file and I'm testing some of the fields, like:
If(Left(B2,3)="A/R","",B2)

One of the cells in column B contains - in the 1st position the square
Box Character, then 1234, sorta like x1234 (where x is the square
box character)
How can I incorporate this character into my above formula?

Is the square box normally visible in the cell, or do you see only when
you edit the cell?

An "invisible" square box might represent a forced new-line, forcing
"1234" to be in the second line of the cell. If that is true in your
case, you __could__ type "<alt-Enter1234" as a string, where
<alt-Enter represents those keystrokes. Alternatively, you might
consider removing the square box in the cell and instead changing the
cell vertical alignment to Bottom (Format Cells Alignment
Vertical Bottom).

If the square box is always visible in the cell, then I presume it
comes from a font that contains that character. Perhaps if you
highlight just the square box, Excel will tell you want font it is.
But I do not know how you determine the character number within the
font.

Help with formula
 

Thanks, When I saw this:

=if(left(b2,4)=char(10)&"a/r", ....

I knew what to do (since I had previously
Applied the formula =CODE(LEFT(B2,1)) and gotten 12;

So I used

If(OR(Left(B2,3)="A/R",Left(B2,1)= Char(12)),"",B2)

That did it, Thanks for the help!!

Jim May


" wrote in message
:

Do you know what that box character is?

If no:
=if(mid(b2,3)="123", ....

If yes, include it in your test.

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm

=if(left(b2,4)=char(10)&"a/r", ....

replace char(10) with the code you find using Chip's addin.

Jim May wrote:

I've imported a text file and I'm testing some of the fields, like:

If(Left(B2,3)="A/R","",B2)

One of the cells in column B contains - in the 1st position the square
Box
Character, then 1234, sorta like x1234 (where x is the square box
character)

How can I incorporate this character into my above formula?

Tks in advance...

--

Dave Peterson