We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

How to Convert "John;Smith" to "Smith;John"

asked 2020-07-15 05:58:56 +0200

kasloman gravatar image

The Text functions don't seem to have the oomph to do this. I looked up split() but that seems like a huge effort.

Appreciate help.


edit retag flag offensive close merge delete


Where do you get the information on REGEX to do this REGEX(A1;"([^;]);(.)";"$2;$1";"g") or Find: & replace. The help in LO calc is hopeless. This is a pretty crappy Example: Example =FIND(76;998877665544) returns 6. I looked up REGEX() in the LO calc doc and it is pretty skimpy also. Where is help on $2 and $1. I am new to this site so don't know if this is the place to ask this stuff or do I need to ask another question!!

kasloman gravatar imagekasloman ( 2020-07-16 03:15:15 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2020-07-15 07:23:45 +0200

JohnSUN gravatar image

updated 2020-07-15 15:14:19 +0200



where A1 is cell with your source "John;Smith"

Also you can use Find&Replace whith this options

Find: ([^;]*);(.*)
Replace: $2;$1
Regular expresions: ON

Update A macro function that performs the same task.

Function reorderParts(sSource As String, Optional sDelimiter As String, Optional sOrder As String) As String 
Dim aTemp As Variant 
Dim aOrders As Variant 
Dim sTemp As String
Dim i As Integer 
    If IsMissing(sOrder) Then sOrder = "2,1"
    aOrders = Split(sOrder,",")
    If IsMissing(sDelimiter) Then sDelimiter = ";"
    If InStr(sSource, sDelimiter) = 0 Then 
        reorderParts = sSource
        Exit Function ' Empty string or no delimiter'
    aTemp = Split(sSource, sDelimiter)
    sTemp = ""
    For i = LBound(aOrders) To Ubound(aOrders)
        If i > 0 Then sTemp = sTemp + sDelimiter
        If Val(aOrders(i))<1 Or Val(aOrders(i))-1 > UBound(aTemp) Then 
            sTemp = sTemp + "Wrong index"
            sTemp = sTemp + aTemp(Val(aOrders(i))-1)
    Next i
    reorderParts = sTemp
End Function

Examples of using


edit flag offensive delete link more


hello @JohnSUN,

nice work, i like to learn from such exercises, but do not feel save against simple minded creative users ...

just a quick shot from memory reg. macro-function weaknesses:

  1. you can't debug 'em because you can't pass the parameters, message 'argument not optional', you might get questions about that,

  2. they fail to update the sheet on changes in the function, e.g. change the delimiter and the results in the sheet will stay as before, things like this overtax standard users awareness and will produce unnoticed wrong results,

  3. and a short stresstest, trying chained 'reorderparts' functions and changing the delimiter produced noticeable delays for recalc with F9, changing it to ";;" and back to ";" fixed the mousepointer as 'wait', deleted the content of one cell and disabled input on the sheet, tried to close: crash (ver.

you are trying to produce stable routines ...(more)

newbie-02 gravatar imagenewbie-02 ( 2020-07-15 23:56:41 +0200 )edit

save against simple minded creative users - just a quick shot

Hmmm... This sounds like a possible solution. Not very humanistic, but this is one of the solutions :-)

The reorderParts function does not involve much experimentation and configuration. Select the necessary parameters for a sample from one cell, make sure that the result meets expectations, multiply the resulting formula for all data, get rid of the formula (or Copy-Paste Special-Text, or Data - Text to Columns)

Isn't that KISS? Perhaps you and I understand this principle in different ways. In my version "stupid" is not mentioned, for me it is Keep It Small and Simple

JohnSUN gravatar imageJohnSUN ( 2020-07-16 09:39:40 +0200 )edit

answered 2020-07-15 11:35:49 +0200

newbie-02 gravatar image

a simple formula solution for those who are not familiar with regular expressions and don't want to do macro programming:

B3: 'John;Smith' 
C3: '=RIGHT(B3;LEN(B3)-SEARCH(";";B3)) & ";" & LEFT(B3;SEARCH(";";B3)-1)' without the quotes,

and a quick shot per macro:

sub test_re_arranging_strings

' cumbersome, 'wooden,' but it works, 
' can also be run over areas with 'GetCellByPosition(i,j)' and 'run variables' for i and j, 

myDoc = thisComponent
mySheet = myDoc.sheets(0)   'other values for other sheets

mycell = mysheet.getCellRangeByName("B2")
   aParts = Split(mycell.string, ";")
   sNewString = aParts(1) & ";" & aParts(0)   'takes only first and second split part, care if source contains more ";"

mycell1 = mysheet.getCellRangeByName("C2")
   mycell1.string = sNewString

end sub 'test_re_arranging_strings

have fun ...

edit flag offensive delete link more


care if source contains more ";"

What about source without any ";"?

And what about empty cells?

JohnSUN gravatar imageJohnSUN ( 2020-07-15 11:45:28 +0200 )edit

@JohnSUN: 'multiple', 'empty', 'without' ";" ... as always ... 'garbage in -> garbage out',

or try to write comprehensive error handlers, and learn that users can construct more comprehensive exotics, there are simply more users around with more creativity and stupidity than programmers, 'they win',


seriously: when working interactively the user sees the #VALUE! messages and can correct the errors in the source data, multiple ";" i mentioned because results are produced which are not immediately noticeable as errors, but may deviate from users expectations.



newbie-02 gravatar imagenewbie-02 ( 2020-07-15 13:53:57 +0200 )edit

Yes, you are right - it’s not stupid users who are to blame for any mistake, but programmers.

Especially if instead of the usual #VALUE! stupid user will see this


Therefore, when proposing a solution, we need to protect us as much as possible from possible reproaches. I updated my answer.

JohnSUN gravatar imageJohnSUN ( 2020-07-15 15:14:23 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-07-15 05:58:56 +0200

Seen: 84 times

Last updated: Jul 15 '20