If cells contains certain text add on X-Large to end of text in column next to it

[illinny]

Hi Everyone,

I'm having a good amount of trouble doing this formula even though I've done it in the past. Someone help?

If any cells in column A contain the text "-XL" add "- X-Large" onto the end of the respective cells in column B.

Thanks in advance!

[hemesh]

1.the text"-XL" can be anywhere or will be at the end
2.You want to remove "-XL" and add "-X-Large" or keep "-XL" and Add "-X-Large"

[Pete_UK]

Insert a new column C if you already have data in that column, then put this in C2:

=IF(ISNUMBER(SEARCH("-XL",A2)),B2&"- X-Large",B2)

then copy down to the bottom of your data. Fix the values in column C and delete column B.

Hope this helps.

Pete

[illinny]

Thank you Pete! Appreciate it!

[illinny]

Pete (or anyone),

One more question. If I want to add more 'if' formulas to search column A and give different results in the new column C, how would I do that?

I want to search column A for say "-XL", "-L" and "-M". Depending on which result is found I want column C to give the result of either " - X-Large", " - Large" or " - Medium".

Help? Thanks again in advance!

[illinny]

I meant I want column C to add those results onto the end of what's in column B. Thanks

[Pete_UK]

You could do something like this:

=IF(ISNUMBER(SEARCH("-M",A2)),"- Medium ","") & IF(ISNUMBER(SEARCH("-L",A2)),"- Large ","") & IF(ISNUMBER(SEARCH("-XL",A2)),"- X-Large","") & B2

then copy down. This assumes that you could have any one (or none) of those identifiers in column A.

Hope this helps.

Pete