[Solved] Text translate/replace question

[lesbentley]

Solved.
I need to take an abbreviation in one column, and output the equivalent full Latin name in another column. I have a table of equivalents, but don't know how to proceed. Please explain this in simple words, as I am very new to spreadsheets.

I'm hoping this can be done. One way to describe the problem would be, copy all the data from column A into column B, then run multiple "Find & Replace" operations on the data in column B based on the conversion table (see below). I'm sure there are many other ways to express and approach the problem. "Find & Replace" may not be the best approach, I just used it as a way to describe the problem. the only really important thing is the result.

In the image below, it is column B that needs to be automatically filled in according to the conversion table.

[Zizi64]

Please explain this in simple words, as I am very new to spreadsheets.

Use the VLOOKUP() function. Please read the HELP.
https://help.libreoffice.org/Calc/Sprea ... ns#VLOOKUP
https://wiki.openoffice.org/wiki/Docume ... P_function

In the cell B4:

Code: Select all

=VLOOKUP(A4;$E$4:$F$50;2;0)

...$F$50 or more cells in your existing range... I can not see the "last" cell of your data table on the attached picture. It is better to upload a real ODF type sample file here.

[lesbentley]

Thanks Zizi64, that formula seems to have done the job.

But I now realize that it only solves part of the problem. Column A will hold a varying number of cells with data in them, there could be anywhere between one and several thousand entries in column A. Cells with data in them will always be contiguous, there will be no gaps until the end of the data.

Is there a way that I can apply the "VLOOKUP()" function to all cells in column B that are opposite cells in colmn A that contain data, but not past the end of the data?

Vloop-TEST-0001.ods

This is what I have so far.

(13.09 KiB) Downloaded 60 times

[Zizi64]

Code: Select all

=IF(ISBLANK(A4);"";VLOOKUP(A4;$E$4:$F$50;2;0))

[MrProgrammer]

Hi, and welcome to the forum.

Is there a way that I can apply the "VLOOKUP()" function to all cells in column B that are opposite cells in colmn A that contain data, but not past the end of the data?

[Tutorial] How do I specify the formula for a column? says "double click its fill handle to replicate the formula to match the data." But you really should read the entire tutorial to learn more about the process.