[Solved] Convert mixed string to text

Richard1 · Post by **Richard1** » Mon Sep 21, 2009 1:08 am

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?

edit this topic's initial post · Post by **Villeroy** » Mon Sep 21, 2009 1:16 am

TEXT(number ; format_code)
Use the same format codes as in the cell formatting dialog. The codes are text values. Literal text values in formulas need to be "quoted".
=TEXT(A1 ; "@") where "@" is the general format code for a decimal number without leading zeroes.
=TEXT(A1 ; "0000") formats the number in A1 as 4-digit number with leading zeroes.

Oh, and when A1 is not a number at all, TEXT must fail:
=IF(ISNUMBER(A1);TEXT(A1 ; "@");A1)

Richard1 · Post by **Richard1** » Mon Sep 21, 2009 1:45 am

Thank you!!!
works like a charm for most.....

But not for the one with a hyphen in the middle. Any thoughts on those?

edit this topic's initial post · Post by **Villeroy** » Mon Sep 21, 2009 9:29 am

Richard1 wrote:But not for the one with a hyphen in the middle. Any thoughts on those?

I have no access to your computer yet, so you should be a little bit more explicit. WHich value, which formula, which result?

In any database (spreadsheet or real one) you should always avoid mixed types. Databases enforce this.
Convert cells to text:
Select the range(s) in question.
Format>Cells... [Number Format], Category:Text (format code "@")
Formatting attributes never change any of your values, but this one inhibits all evaluation. When you enter new numeric values or formulas into the prepared cells, your input will be treated as literal string.
Re-enter everything into the prepared and selected cells:
Edit>Find&Replace...
[More Options...]
[X] Current Selection
[X] Regular Exppression
Search: .+ (a dot and a plus)
Replace: & (ampersant)
[Replace All]
btw: the same works for text-to-number as well after setting any number format other than "@".
A database can save time and effort in the long run. Databases are difficult to set up but fool proof to use. Spreadsheets are a trap since you can start so easy entering anything in every single cell. Quite often this leads into a maintainence nightmare.

Richard1 · Post by **Richard1** » Tue Sep 22, 2009 1:51 am

The replace worked to get it as text in the first column, so I think it is fine now. Thanks.

[Solved] Convert mixed string to text

[Solved] Convert mixed string to text

Re: convert mixed string to text

Re: convert mixed string to text almost solved

Re: Convert mixed string to text

Re: Convert mixed string to text