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

# Remove the first character and all spaces and add characters - formula calc? [closed]

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

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2020-10-04 13:54:03.846385

Sort by » oldest newest most voted

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

more

This is no recognized as a formula here.

( 2017-05-16 15:23:34 +0200 )edit
1

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

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

( 2017-05-16 15:29:15 +0200 )edit
1

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

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

( 2017-05-16 15:51:27 +0200 )edit
1

...inued)
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.
(cont...

( 2017-05-16 15:52:10 +0200 )edit
1

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

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

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

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

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

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

more