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

convert mixed string to text

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


Joined: 20 Sep 2009
Posts: 8

PostPosted: Sun Sep 20, 2009 1:17 pm    Post subject: convert mixed string to text Reply with quote

Please bear with me as after using Excel for 20 years I've just switched to open office, and know I'll be going through a learning curve.
I have a list of products with their unique alfa-numeric codes in a column (2093, 2994, 2094B, 2082-34, etc) in the first column of many sheets.
I need to look up data or sales for each product on various sheets. In order do this with Vlookup in Excel, I had an additional column with a formula "=text(a2;0)". This returns everything as text, where Vlookup can match it on the source sheet.
In Calc, when there is text in the original column (2094B), this formula returns "0" for that cell.

How can I get it to put the 2094B in the second column, or some other way to be able to look at a similar column on the source sheet?
Back to top
View user's profile Send private message Visit poster's website
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Sun Sep 20, 2009 8:43 pm    Post subject: Reply with quote

I hope I understand your question.

I don't believe you need an extra column in OOo. VLOOKUP should work with your mixed set of numbers and alphanumeric strings.

FWIW, unlike Excel, OOo doesn't currently treat a number entered as text as a number.

5
text 5
5
will sum as 10.

This is due to change in the future but I don't believe it will ever consider an alphanumeric string as anything but a string.
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Mon Sep 21, 2009 4:09 am    Post subject: Reply with quote

If I understand, and you need a column to be text, then you can precede each item with an apostrophe. If you can save as a CSV file, you can edit that CSV readily and quickly to add the prefix using a freeware WIndows program, CSVed. If inclined to look at that option, I'd use a small sample file ot experiment first, not working on the original later, but on a copy.

David.
Back to top
View user's profile Send private message
Richard1
General User
General User


Joined: 20 Sep 2009
Posts: 8

PostPosted: Mon Sep 21, 2009 4:20 am    Post subject: Reply with quote

If I have it look directly at the first column, where some are numbers and some are mixed, Vlookup returns the values for the mixed, but not for the pure numbers.
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Sep 21, 2009 6:36 am    Post subject: Reply with quote

VLOOKUP(A1;$X$1:$Z$999;3;0) returns from Z where A1 matches in X. If A1 is a number, then it matches at the first ocurrence of that number, if A1 is a text, things become complicated.
Turn OFF all check boxes in Tools>Options...Calc>Calculate except for "match whole cells" in order to get case insensitive matches of literal strings. Turn on case sensitivity if needed, but stay away from regular expressions unless you are shure about pattern matching, otherwise you may get wrong results.
In http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=22777 I describe how to convert everything to text.

All the pitfalls of matching text values and substrings with and without regexes: http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=22759
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Mon Sep 21, 2009 7:06 am    Post subject: Reply with quote

Quote:
If I have it look directly at the first column, where some are numbers and some are mixed, Vlookup returns the values for the mixed, but not for the pure numbers.
In my case if column A contains text and the lookup table contains numbers they match. If column A contains numbers and the lookup table contains text then they don't match.

=VLOOKUP(A1;C$1Very Happy$4;2;0)

FWIW, I have everything except the Precision item checked at Tools > Options > Calc > Calculate.
v3.1.1 under Vista 64-bit.

Converting text numbers to numbers. Use Find & Replace.
Search = ^.
Replace = &
Check Regular Expressions
Replace All
I don't know if this will work on a file originally created with Excel.
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