Remove the first character and all spaces and add characters - formula calc?

Would be great to have now.
077 333 22 11 shall become +41773332211
05522 35 155 > +41552235155

=REPLACE(SUBSTITUTE(A1," ",""),1,1,"+41")

This is no recognized as a formula here.

You should pay attention to two things:

  1. Function names. In some locales, they are localized; so you might need to change their names correspondingly, or switch to English function names (Tools-Options-LibreOffice Calc-Formula-Use English function names).
  2. Function arguments separators. Here on Ask, it’s usual to use , as separators, but some locales use e.g. ; for that.

Thank you, Mike. These are already already. Relax I found the answer.

Quoting @Mike kaganski: “Function arguments separators. Here on Ask, it’s usual to use , as separators, but some locales use e.g. ; for that”.
If so, thats bad. (cont…

You changed your answer. This is elegant and simple. Thank you.

(I tried to adapt my comments, but the system does not allow this.)


  1. The originally mandatory parameter delimiter in OpenOffice and its predecessors was the semicolon. The comma in this place was a bagly considered concession to the americanized world.
  2. It aggravates of interchange of solutions / help / questions acrross the brancehs of OOo successors. For AOO the semicolon still is mandatory.
  3. The semicolon is still the one parameter delimiter accepted by all the locales.

4) Bad: In “decimal-point.locales” formulas are displayed with commas in the delimiter position. This should not afflict the RAM representation, and for the persistent representation (file) the semicolon still is mandatory: Separator ::= ';' (‘OpenFormula’, p44, first line).
5) User now can select this separator under ‘Options’. (Also a bad idea in principle. Good for me now, because I mostly work under English UI, and don’t want to change …)

@Lupp: Well, I agree that it’s inconvenient for many (e.g. for me: I have to replace my ;s with ,s every time, and sometimes suggest addressee to replace them back :slight_smile: ). But that’s user demand in the end; many things are inconvenient for me (e.g. 5.3 was the first to feature Russian localization of function names in Calc - very much annoying thing). I have to comply if it’s better for users.

(Just to assure: yes, format-wise, inside the ODF XML, normal ; is (and always will be) used.)

Not only about user demand: =SUM(1,2,3,4,5,6,7,8) would be a syntactical chaos in a cell applying a “decimal-comma-locale”. Excuse me if I insist: To handle this like a flavor the user should choose as they like was is a very bad idea. Probably individualization will one day destroy its basis. Free software is a field where this can have a start.
At least some help given here may grow to double lenght or more if lots of setting need be mentioned.

(The majority of locales do not use the decimal point. For general purpose programming and for pocket calculators I always accepted the point since 1965. Why shouldn’t I expect “decimal-dot-people” to also accept something different from what they have in the kitchen?) They won’t be less clever, will they?.

Is there a way to unsubscribe from this side topic comments?

:slight_smile: Well, this is the LO feature since 3.3 (and I suppose, it was a feature of GoOO). Still, I don’t see this as a major issue here.

I dislike the localized nature of format strings more (which is also true for OOo). That the decimal and thousands separators are localized in cell format simply maddens me, and I don’t see any documentation or configuration on that… but I have to comply, again.

Oh, sorry. I don’t know how to unsubscribe; but no more messages from me here. @Lupp: welcome to email discussion if you like.

I have searched the internet for the aspects of this operation and brought together my solution.
If somebody needs the formulas …

                   =SUBSTITUTE(A3," ","")    =MID(C3,2,20)    =CONCATENATE("+41",D3) 
077 555 44 33	     0775554433	                   775554433         +41775554433

Mikes new answer below works for me and is more elegant.