IF Statement HELP!?

[chadshowalter]

I hope that one of your Excel Geniuses can help me out with this.

I have a master product file spreadsheet. When I exported from my Content Management System into a CSV file it created one column to designate the categories each product has assigned to it. For example, "Product A" falls into categories 817, 456, 234, 081, and 450.

THE PROBLEM

I need to identify one category within this string, say Category 456.

Excel understands this column to be one number, "817,456,234,081,450"

How can I write a IF formula that would seek out ",456," and populate a new cell with a new value?

I have tried changing the format of the column to text, however, then I lose all the comas, which creates a huge mess for me, because I can't tell the categories apart from each other (some categories have 3 numbers, some have 4, etc.)

I have tried a traditional IF statement, but it will find any occurrence of 456, as in " 134,560"

Any ideas would be greatly appreciated. I'm a newbie, so please be easy on me!

Chad

 

[rallcorn]

There's probably a more sophisticated way of doing it, but this would be a quick way.
Assuming the data you want separated is in Column A
1) In another column enter this formula: =text(A1,"#,###) & copy this
formula down to all the rows you wish to convert
2) Select the entire range of cells that now have the above formula and copy/Paste Special/Values
3) Select the same range then Alt+D+E to get to the "Convert Text to Columns Wizard"
Original Data Type choose "Delimited" > Next
Delimiters check ONLY "Comma" (In Data Preview you'll see it "split" data) >Next
Destination (browse to where you want the individual segments to go) >Finish

 

[F&s anaylst]

Unsure about the search element you are using within the if statement but the below should work for you, just copy it down to end of your recordset. This is the quick way there are number of other ways if it's long term solution you need then you should think about VBA function instead

=if(iserror(search(",456,",A1)),"","found Product A")

 

[BenMiller]

Building on Rallcorn's suggestion, with your data in column A, put this in A2 =MID($A1,COLUMN()*3-5,3)

Then drag down and across as needed, then copy and paste values.

 

[BenMiller]

Unsure about the search element you are using within the if statement but the below should work for you, just copy it down to end of your recordset. This is the quick way there are number of other ways if it's long term solution you need then you should think about VBA function instead

I don't think the commas are part of the value - if Excel understands it to be a number, it is only displayed with commas.

Also, what if the three digits were at the beginning or end of the string?

 

[Scott Huish]

Why not do a Text to Columns with commas as the delimiter to separate the values?

 

[BenMiller]

Why not do a Text to Columns with commas as the delimiter to separate the values?

He says when he changes the values to text he loses the commas - meaning they're only being displayed as part of the number. I believe you can't TTC based on that, though I may be mistaken.

 

[chadshowalter]

Thank you all for your comments, I'm working through them now and I'll report back!

Chad

 

[rallcorn]

He says when he changes the values to text he loses the commas - meaning they're only being displayed as part of the number. I believe you can't TTC based on that, though I may be mistaken.

You cannot TTC if the commas are only displayed as a number separator . . . which is why I went through the exercise in steps 1 & 2 of post #2 to turn it into text and then do TTC. Round about way to get where he needs to be, but it works & is fairly quick.

 

[BenMiller]

You cannot TTC if the commas are only displayed as a number separator . . . which is why I went through the exercise in steps 1 & 2 of post #2 to turn it into text and then do TTC. Round about way to get where he needs to be, but it works & is fairly quick.

You're preaching to the choir .. follow the thread