Help with formula

[Jim May]

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]

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.

 

[Dave Peterson]

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

Oopsie!

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.

 

[joeu2004]

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-Enter>1234" 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.

 

[Jim May]

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