Replacing two values within one cell

[Saladin]

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.

[Zizi64]

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...)

[RoryOF]

Just to be clear on this: you are wishing to find and then transpose the first two numbers in each dimension sequence?

[Zizi64]

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.

[Saladin]

Thanks for the answers! Yes, I want to transpose first two numbers.

[Zizi64]

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.

[Villeroy]

regex_formulas.ods

split string by regular expressions

(16.27 KiB) Downloaded 114 times