|
||||||||||||||||||||
|
|||||||||||||||||||||
|
#1
07-16-2008, 11:16 AM
|
|||
|
|||
|
advanced find and replace/apend
Hi
i am working with datafeeds and i want to change some of the contents of the fields based on other fields. example field j is a product name but it contains some extraneous info on the end. example "sony television - extended warranty 1234" and field M contains the actual information i want about the warranty example "3 years parts and labour + first 1 year onsite" What i want to do is process the whole workshet so that it takes everything in field J up to the first hyphen and adds it to the start of field M so the resulting field M would be "sony television - 3 years parts and labour + first 1 year onsite" I would also want this to be selective so it only did that on a row where field J has the text "sony television" in it. Thanks in advance for your replies Sip 
|
|
#2
07-16-2008, 11:23 AM
|
||||
|
||||
|
For row 2 perhaps try
=IF(COUNTIF(J2,"sony television*"),"sony television - "&M2,"")
|
|
#3
07-16-2008, 03:14 PM
|
|||
|
|||
|
i think that would do for the one row. i would ideally want to have one query for all the rows however and i guess a sort of wildcard that picks up whatever is in row j up to the first hyphen.
i think i sort of explained it wrong in the first post basically take everything from line j up to the first hyphen in that line and add it to the start of line M not sure if that makes sense.
|
|
#4
07-16-2008, 03:20 PM
|
||||
|
||||
|
How about:
=LEFT(J2,FIND("-",J2)+1)&M2 copied down... ... but you did say you wanted only the ones with sony television in it to have that concatenation to occur which is what DLL was providing...
__________________
Where there is a will there are many ways. Finding one that works for you is the challenge! Please read the Forum Rules: Rules
|
|
#5
07-16-2008, 04:10 PM
|
|||
|
|||
|
Quote:
I know i did say that in the first post. i am bit of a noob at this so still working out what it is i need i think. thanks for the quick replies though. really great forum.
|
|
#6
07-16-2008, 04:19 PM
|
|||
|
|||
|
that worked perfectly.
Could you please direct me to a post or quick guide which would explain how to copy it down. I apologise for my very noobie questions but i guess i have to start somewhere. thanks again for your reply
|
|
#7
07-16-2008, 04:25 PM
|
||||
|
||||
|
You can simply copy the cell that the formula is in..
select the remainder of the range and paste. or ... you can click and hold the little black square at the bottom right corner of the cell with the formula in it.... and drag that down as far as you need.
__________________
Where there is a will there are many ways. Finding one that works for you is the challenge! Please read the Forum Rules: Rules
|
|
#8
07-16-2008, 04:48 PM
|
|||
|
|||
|
brilliant. that worked perfectly.
Is there a way of using IF statements for cells also? for instance if k = "sony television" then x = http://www.thumbnail or something similar. I want to create a field which points to a thumbnail image by URL but i obviously need to make sure i use the correct thumbnail. any suggestions on how best to do this would be most welcome thanks again
|
|
#9
07-16-2008, 04:55 PM
|
||||
|
||||
|
You probably need to create a table of all the items and corresponding url's then use a vlookup() formula (see Vlookup in Excel help) to extract the appropriate URL.
e.g =Vlookup(K2,$X$1:$Y$100,2,FALSE) this would look up the item in K2 in table occupied by X1:Y100 and find the corresponding item in column Y... and put it in the cell with the formula.... To make it an active hyperlink...surround formula with Hyperlink() function... =Hyperlink(Vlookup(K2,$X$1:$Y$100,2,FALSE))
__________________
Where there is a will there are many ways. Finding one that works for you is the challenge! Please read the Forum Rules: Rules
|
 |
| Bookmarks |
New topics in Excel 2007 Help
|
|
|
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
