Ask Your Question

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

asked 2017-05-16 14:52:55 +0200

inJesus gravatar image

updated 2017-05-16 14:55:28 +0200

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

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2017-05-16 15:06:10 +0200

updated 2017-05-16 15:40:41 +0200

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

edit flag offensive delete link more


This is no recognized as a formula here.

inJesus gravatar imageinJesus ( 2017-05-16 15:23:34 +0200 )edit

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.
Mike Kaganski gravatar imageMike Kaganski ( 2017-05-16 15:27:31 +0200 )edit

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

inJesus gravatar imageinJesus ( 2017-05-16 15:29:15 +0200 )edit

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

Lupp gravatar imageLupp ( 2017-05-16 15:48:19 +0200 )edit

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

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

inJesus gravatar imageinJesus ( 2017-05-16 15:51:27 +0200 )edit

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.

Lupp gravatar imageLupp ( 2017-05-16 15:52:10 +0200 )edit

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 gravatar imageLupp ( 2017-05-16 16:04:29 +0200 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2017-05-16 16:10:26 +0200 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2017-05-16 16:11:37 +0200 )edit

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.

Lupp gravatar imageLupp ( 2017-05-16 16:21:26 +0200 )edit

answered 2017-05-16 15:27:51 +0200

inJesus gravatar image

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
edit flag offensive delete link more


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

inJesus gravatar imageinJesus ( 2017-05-16 15:52:21 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-05-16 14:52:55 +0200

Seen: 1,704 times

Last updated: May 16 '17