View previous topic :: View next topic 
Author 
Message 
Richard1 General User
Joined: 20 Sep 2009 Posts: 8

Posted: Sun Sep 20, 2009 1:17 pm Post subject: convert mixed string to text 


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 alfanumeric codes in a column (2093, 2994, 2094B, 208234, 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 


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

Posted: Sun Sep 20, 2009 8:43 pm Post subject: 


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 


David Super User
Joined: 24 Oct 2003 Posts: 5668 Location: Canada

Posted: Mon Sep 21, 2009 4:09 am Post subject: 


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 


Richard1 General User
Joined: 20 Sep 2009 Posts: 8

Posted: Mon Sep 21, 2009 4:20 am Post subject: 


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 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Mon Sep 21, 2009 6:36 am Post subject: 


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 


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

Posted: Mon Sep 21, 2009 7:06 am Post subject: 


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$1$4;2;0)
FWIW, I have everything except the Precision item checked at Tools > Options > Calc > Calculate.
v3.1.1 under Vista 64bit.
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 




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
