Hi!
I am using LibreOffice Calc. I have one sheet with more than 1000 records, each contains dimensions of a product in milimeters. I have to replace first two numbers in each cell, for example:
I have "200 x 100 x 88 mm (box)", and I need to change it into "100 x 200 x 88 mm (box)". I would be very grateful if somebody could help me writing macro for it.
Replacing two values within one cell
Replacing two values within one cell
LibreOffice 5.2.2.2 on Windows 10
Re: Replacing two values within one cell
Just use the Find and Replace function:
Search:
200 x 100
Replace:
100 x 200
...and you can use the 'Regular expressions' option, and codes for automatize this task. (If you have different numbers...)
Search:
200 x 100
Replace:
100 x 200
...and you can use the 'Regular expressions' option, and codes for automatize this task. (If you have different numbers...)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Replacing two values within one cell
Just to be clear on this: you are wishing to find and then transpose the first two numbers in each dimension sequence?
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: Replacing two values within one cell
For the macro solution:
Create a nem user defined cell function.
The input parameter of the function will be the original string (passed from a cell, when you call the function).
Get the position of the first character sequence ' x ' (space, x, space) by the InStr() function into an integer variable. And then get the position of the second one. (See the description of the function InStr() in the Basic HELP.)
Then use the MID(), LEFT(), RIGHT() functions for split the parts of the original string into some string type variables.
Finally concatenate the parts of the original string in the desired order.
The Basic cell function will be return with the new string.
Create a nem user defined cell function.
The input parameter of the function will be the original string (passed from a cell, when you call the function).
Get the position of the first character sequence ' x ' (space, x, space) by the InStr() function into an integer variable. And then get the position of the second one. (See the description of the function InStr() in the Basic HELP.)
Then use the MID(), LEFT(), RIGHT() functions for split the parts of the original string into some string type variables.
Finally concatenate the parts of the original string in the desired order.
The Basic cell function will be return with the new string.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Replacing two values within one cell
Thanks for the answers! Yes, I want to transpose first two numbers.
LibreOffice 5.2.2.2 on Windows 10
Re: Replacing two values within one cell
Or you can write a Subroutine instead of the user defined Cell function. You can lanch the Sub after selecting a cell range. The sub will examine and translate the strings in the cells cyclically on the whole selected range.
You must get the current selection (the selected cell range) by your subroutine, and need a "for-next" or other cyclic code for the conversion of the content of the selected cells. You can get the strings from a specific cell, and you can transpose the text as it was described at my "Cell function" solution, and finally you can put back the modified string into the cell.
In this case you not needed use formulas (cell functions) and you get the result in same cells as the original texts are located.
You must get the current selection (the selected cell range) by your subroutine, and need a "for-next" or other cyclic code for the conversion of the content of the selected cells. You can get the strings from a specific cell, and you can transpose the text as it was described at my "Cell function" solution, and finally you can put back the modified string into the cell.
In this case you not needed use formulas (cell functions) and you get the result in same cells as the original texts are located.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Replacing two values within one cell
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice