If characters are specficially this then change current cell

[legnak]

Hello!

I need to have a rather long if statement that sorts out through the characters in a cell and if the characters are present, then it changes the cell that the formula is in:

Example:

LV.CH.1.1 If that cell has CH in that spot then this cell is Furniture

LV.CH.134.1.Grey If that cell has CH in that spot then this cell is Furniture

then after a while of cells it would look like:

LV.CH.1.11 IF that cell has CH or TB or .. or .. in that spot then this cell is Furniture
LV.CH.1.5 IF that cell has CH or TB or .. or .. in that spot then this cell is Furniture
LV.CH.50.1 IF that cell has CH or TB or .. or .. in that spot then this cell is Furniture
LV.TB.51.1 IF that cell has CH or TB or .. or .. in that spot then this cell is Furniture

etc like that

I hope it makes sense. I'm having trouble with the locating of the CH, TB, CD, etc without the rest of the cell like, specifically 3 characters in and then look at the 2 and skip the rest.

THANK YOU

[mehmetcik]

Unless I am missing something all you need is the mid function.

If your data is in A1 then this formula in B1

Please Login or Register  to view this content.

[legnak]

THANK YOU!

I was getting very confused. This is amazing.

[Phuocam]

Or try this ...

=IF(OR(MID(A1,4,2)={"CH","TB"}),"Furniture","")

[legnak]

Unless I am missing something all you need is the mid function.

If your data is in A1 then this formula in B1

Please Login or Register  to view this content.

This is what I have adapted it to:

Please Login or Register  to view this content.

and it seems to be backwards/not working properly? Any suggestions?

[mehmetcik]

In your sample you used two characters for code.

Here you are using 3 in some instances and 4 in one instance.

It is still possible but would be easier with a user defined function.

Please Login or Register  to view this content.

This code will achieve the same result using the formula:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

To install the code, click on the developer tab in excel
Click on Visual Basic
Click on insert and then Module

Paste this code and close visual basic.


Hopefully you can see that this solution would be a lot easier to maintain.

[legnak]

In your sample you used two characters for code.

Here you are using 3

Ahh, I see I need to change my ThB/ThP/Etc to 2 character abbreviations.
Okay, I can probably do that. However, Let's say that some of these 3
character ones are crucial is there a work away around that?

[mehmetcik]

I have provided two workable solutions for you in my last post.

[legnak]

Okay, I got the longer piece working, but can't get the module working. Which isn't the end of the world because I can just use the if statements.

[legnak]

I have provided two workable solutions for you in my last post.

When my characters are DN.TLa.1.1, it displaying LightingFlooring. Probably because TL is flooring and TLa is lighting, any suggestions?

[mehmetcik]

Move the If statement for lighting before the statement for flooring

I will insert the code into a sample spreadsheet for you. That is the way ahead.

[legnak]

Move the If statement for lighting before the statement for flooring

I will insert the code into a sample spreadsheet for you. That is the way ahead.

Okay, this is working awesomely, but now I have another set of cells that have characters that look like this: DN.B.CH.1.1 is there a way we could modify the code above to make it work with the extra characters in there?

Thank you!!

[mehmetcik]

I need more details.

[legnak]

I need more details.

Before it looked like;
LV.CH.1.1
LV.Tla.1.1

Now it looks like;
LV.D.CH.1.1
LV.C.Tla.1.1

with the extra character before the important CH.

Still wanting it to do the exact same thing with the exact same output, but now there are 2 extra characters in the cell

[mehmetcik]

Ok are you using formulas or the user defined function?

Revised formula:

Please Login or Register  to view this content.

Revised UDF:

Please Login or Register  to view this content.

[legnak]

The function seems to be working! Thank you so much! Will mark as solved until I do some further testing, thank you.

[legnak]

Ok are you using formulas or the user defined function?

Revised formula:

Please Login or Register  to view this content.

Revised UDF:

Please Login or Register  to view this content.

Once I saved it all went away and reset the function. Is there a specific format I should be saving in ?