convert mixed string to text

Richard1 · General User Joined: 20 Sep 2009 Posts: 8

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?

JohnV · 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.

David · 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.

Richard1 · General User Joined: 20 Sep 2009 Posts: 8

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.

Villeroy · 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 http://forum.openoffice.org

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