Adding A Function On A Column

[tomyknoker]

I have a column which has either EDC or EDT in it, can I add a function to this column which says 'if EDC then output Eau De Toilette' and then Eau De Toilette get's ouputted into a seperate column? Is this kind of thing even possible in Excel?

[SamuelT]

Hi,

In a new column, put the following (presuming, EDC is in A1):

=IF(A1="EDC", "Eau De Toilette","")

The inverted comma's after "Eau De Toilette" can have another value in them, e.g. =IF(A1="EDC", "Eau De Toilette","Moisturiser"). This will mean that if EDT is not in A1 then the cell value will be returned "Moisturiser". If you just put "", it will return a blank.

HTH,

SamuelT

[tomyknoker]

Wow thank you! There are 1200 rows in that column actually so do I just select all of them for the sum? And also how can add alternatives, with an else statememnt?

=IF(A1="EDC", "Eau De Toilette","")...

I tried to get it to work but no luck... Does only EDC have to be in that column? There is other infoi in there as well...

Also how do I get it to basically check each row and then add the result to the row next to it?

[SamuelT]

Hi,

You could do something like this:

=IF(A1="EDC","Eau De Toilette",IF(A1="WC","Wrinkle Cream",IF(A1="NC","Nail Clippers","No Product")))

It depends on how many product codes you have (you can do up to seven, although OR statements could extend this). The "No Product" at the end of the statement basically says that if it's none of these then display "No Product".

To use in your 1200 rows just drag the formula down, or copy the formula cell, select your 1200 rows and paste.

SamuelT

[tomyknoker]

Samuel thanks! The only issue I am having now is that when it looks in the column say for 'EDC' if the column has (which is always the case) 'PRODUCT NAME EDC', it doesn't recognise that because it is looking exactly for 'EDC' can I add anything to either side of the statement to ignore extra text? Or even do a LIKE statement?

[SamuelT]

Hi,

Yup - this can be done by adding the COUNTIF function with wildcards (*) to the statement. So the last example becomes:

=IF(COUNTIF(A1,"*EDC*"),"Eau De Toilette",IF(COUNTIF(A1,"*WC*"),"Wrinkle Cream",IF(COUNTIF(A1,"*NC*"),"Nail Clippers","No Product")))

SamuelT

[SamuelT]

You forgot to add the extra COUNTIF function

=IF(COUNTIF(C2,"*EDT*"),"Eau De Toilette Spray",IF(COUNTIF(C2,"*EDP*"),"Eau De Parfum Spray",IF(COUNTIF(C2,"*EDC*"),"Eau De Cologne Spray","")))

[tomyknoker]

Um wow Samuel! That is perfect! Ok my next bit is maybe more tricky, well the way I am thinking it could be done...

Say in my description I have "PRODUCT NAME EDT 30ML", every row has a "00ML" value, can you move all these in each row to another row called size? Or would I be best of just using the above mehtoed for this also and just adding every option? The only thing is as there are 1200 products these are always different. There are standard ones like 75ML, 100ML and 150ML but also many other sizes...

I'm stunned at what can be achieved with Excel!

[SamuelT]

Yup - this can be done too! Presuming all the sizes are always 4 digits long, you can do:

=right(c2,4)

[tomyknoker]

I see what that does cool... The only issue not all the sizes would be 4 characters long, which would be ok if it just added extra space. The main issue though is not all the ML are at the end some are in the middle, like "PRODUCT NAME 30ML EDT"... Any ideas?

[SamuelT]

OK - this'll give you any length if the ML value is at the end:

=RIGHT(c2,LEN(c2)-FIND("*",SUBSTITUTE(c2," ","*",LEN(c2)-
LEN(SUBSTITUTE(c2," ","")))))

Just taking a look at if the ML is in the middle somewhere...

[tomyknoker]

Hi Samuel, that worked wonders for the ML and removed the white space... Any ideas how to target the ML if it's not at the end of the description in the row?

[SamuelT]

Hi,

No, having problems with this. The unknown length of the ML value is causing problems. If anyone else has any ideas...

SamuelT

[Jazzer]

Hi,

with this quite lenghty array formula you can do it:

=MID(A1,MIN(IF(ISERROR(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),LEN(A1)+1,ROW(INDIRECT("1:"&LEN(A1))))),FIND("ML",A1)-MIN(IF(ISERROR(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),LEN(A1)+1,ROW(INDIRECT("1:"&LEN(A1))))))

Assuming your product name with the size in it is in A1, put this in B1 and remember to enter it with Ctrl+Shift+Enter, because it is an array formula. If there is no size in the product name, this will return an error.

I'm sure there is a shorter way, but this is the first thing I came up with.

- Asser

[Jazzer]

I found a little shorter array-formula (by Peo Sjoblom) to do the same thing:

=MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),LEN(A1)-SUM(1*ISERROR(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))*1

- Asser

[SamuelT]

And now the easy bit

To get the "ML" at the start, do the array formula with this small appendment at the start.

="ML"&(MID(A6,MATCH(FALSE,ISERROR(1*MID(A6,ROW(INDIRECT( "1:"&LEN(A6))),1)),0),LEN(A6)-SUM(1*ISERROR(1*MID(A6,ROW(INDIRECT("1:"&LEN(A6))),1))))*1)

I feel like Alan Shearer...

[tomyknoker]

Wow thanks guys I only just saw this post I'll give it a try!!!