CODEWITHSUNDEEP
excelif-statementcharacter
user1830307
user1830307

Reputation:

Boolean check in Excel, checking only first character

I have an Excel sheet with data only in the first column, where each cell starts with the character H, G, I, B, H, E, S, C, or none of the above.

I would like to write a command so that in the second column, one of four things will happen:

  1. If the cell in the first column starts with H, G, or I, then the cell in the second column is assigned H
  2. If the cell in the first column starts with B or E, then the cell in the second column is assigned E
  3. If the cell in the first column starts with T, S, or C, then the cell in the second column is assigned C
  4. If the cell in the first column starts with none of the above, then the cell in the second column remains blank

I found that this command does part of the problem:

=IF(OR(B25="H"; B25="G"; B25="I");"H";"") & IF(OR(B25="B"; B25="E");"E";"") & IF(OR(B25="T"; B25="S"; B25="C");"C";"")

However, this only works if the first column only contains one character. I would like to tailor this command so that it does not require there to only be one character in the first column, but that it simply starts with the character.

I hope this makes sense. Please let me know if you have any advice!! :O)

Upvotes: 1

Views: 9506

Answers (3)

bto.rdz
bto.rdz

Reputation: 6720

try this:

=IF(OR(LEFT(AB25,1)="H"; LEFT(AB25,1)="G"; LEFT(AB25,1)="I");"H";"") & IF(OR(LEFT(AB25,1)="B"; LEFT(AB25,1)="E");"E";"") & IF(OR(LEFT(AB25,1)="T"; LEFT(AB25,1)="S"; LEFT(AB25,1)="C");"C";"")

Upvotes: 1

andy holaday
andy holaday

Reputation: 2302

Create a small table elsewhere in in the workbook. Let's give it the named range "MyLookup" for the sake of example. This table simply maps the input values to the desired output values, like so:

MyLookup:
Input    Output
H        H
G        H
I        H
B        E
E        E
etc...

Now use this very simple formula to get your result:

=IFERROR(VLOOKUP(LEFT(B25,1),MyLookup,2,FALSE),"")

Make sense?

Upvotes: 1

Sandmann
Sandmann

Reputation: 21

Maybe the "LEFT()"-Function is what you are looking for.

Something like "LEFT(A1,1)" should give you the first Character of the String in Cell A1.

More information: http://office.microsoft.com/en-us/excel-help/left-leftb-functions-HP005209153.aspx

EDIT: Wow, this community is always impressing me. (Too fast for me ;))

Upvotes: 2

Related Questions