Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
2 Nesting questions | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |