OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Help with Concatenate Formula

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
DarkFerret
Newbie
Newbie


Joined: 20 Aug 2007
Posts: 1

PostPosted: Mon Aug 20, 2007 10:17 am    Post subject: Help with Concatenate Formula Reply with quote

Hello, I am trying to use OO Calc to quickly edit a database. this database can export out to csv, then import the csv file.

There are two columns that use the same info that is entered in another, and I would like to use the CONCATENATE formula to autofill these.

In column D, I enter a stock number, for instance [8-327A], in Column E, the database has a link to the image of the item, which is [<img src="%%URLofImages%%/8-327A.jpg" alt="8-327A.jpg">], and in column H, another short link to the small image file of the item, which is [small_8-327A.jpg]. Disregard the [ ] as i used them only to separate the cell values from text.

My problem is that for some reason, when trying to use " " (quotes) the formula fails. If anyone can lend assistance, I would greatly appreciate it. I have to enter over 150 more items into this database, and using the existing product manager I have to enter each value individually. If I could use this formula, I would only have to enter 4 fields of info compared to 9 fields.

Again, Thanks for any help.

Dark Ferret
Back to top
View user's profile Send private message
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Mon Aug 20, 2007 10:37 am    Post subject: Reply with quote

Use the & operator for concatenating. Quotes are a special case when you want to use them in a text constant, because they are text delimiters. Two consecutive double quotes within a string (the string itself enclosed in double quotes) is taken as a double quote. I guess the line below will give you the formula you need for E2:

="<img src=""%%URLofImages%%/"&TRIM(D2)&""" alt="""&D2&""">"

The TRIM() function is not strictly needed, but useful in case you type spaces when entering data.
Back to top
View user's profile Send private message
TerryE
Super User
Super User


Joined: 16 Jul 2006
Posts: 550
Location: UK

PostPosted: Mon Aug 20, 2007 4:02 pm    Post subject: Reply with quote

The RFC which covers URL naming limits the character set which can be directly included in a URL, so letters and digits are OK but most other characters are "escaped" so if I recollect correctly ( becomes %2E. What you need to be careful of ( as kerne eludes to is if you have any such characters in cell D2 (a minus is OK, but not a comma or a space). However if it is just a pure unformatted number -- that is a sequence of [0-9] then willl be OK.

Many languages have a nice build in function for escaping strings, but alas Calc does not. //Terry
_________________
Terry
WinXPSP3, OOo 2.4.1, Ubunto 8.04 for development
Also try the Official OOo Community Forum where I mainly post now.
Back to top
View user's profile Send private message Visit poster's website
dv
Newbie
Newbie


Joined: 20 Aug 2007
Posts: 4

PostPosted: Mon Aug 20, 2007 8:20 pm    Post subject: Reply with quote

Is there any index of the characters and corresponding numbers for html? A blank space is 20 but what number is assigned, for example, to a single inverted comma?
Back to top
View user's profile Send private message
TerryE
Super User
Super User


Joined: 16 Jul 2006
Posts: 550
Location: UK

PostPosted: Tue Aug 21, 2007 4:28 pm    Post subject: Reply with quote

Yup I could give you a list but why don't you just do a google for HTML special chararacters and take your pick Razz //T
Back to top
View user's profile Send private message Visit poster's website
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Wed Aug 22, 2007 5:00 am    Post subject: Reply with quote

TerryE wrote:
The RFC which covers URL naming limits the character set which can be directly included in a URL, so letters and digits are OK but most other characters are "escaped" so if I recollect correctly ( becomes %2E. What you need to be careful of ( as kerne eludes to [ ... ]
//Terry
That's correct, so if you want to catch any and all invalid URLs, you get a fairly complex formula. I guessed that the filenames in question would be valid, but whitespace is often entered by mistake, and not easily spotted, hence the TRIM() error catcher. Mistyped visible characters are easily spotted when entering data, so I assumed the user would catch that.
It's not necessary to clutter up the explanation when dealing with users' own worksheets. It's hard enough to read as it is, and perfectly tokenising a non standard URL can be rather complex. If you develop solutions for an end user, on the other hand...
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group