4

I would like to ask about replacing multiple possible characters in excel. I have some cells containing coordinates like:

(37.286.866, 16.826.515)

(36.491.603, 17.624.765)

(37.297.949, 16.951.373)

I would like to get their value in a new cell without the parentheses and (if possible) the space between like:

37.286.866,16.826.515

To make it clear in a cell both coordinates are included. For example A1 cell contains (37.286.866, 16.826.515) I tried something like (with the intention of using another embedded SUBSTITUTE after that) but is does not seem to work:

=SUBSTITUTE(A1;"(";"-")

Although there is no error just a blank cell.

Any idea how can I achieve this? I am not interested in VB solutions if possible. Just an in-cell solution. The functionality I am looking (if this makes it any clearer) is similar to the regex [()] where any of the previous could be replaced.

I am using MS office 2010.

5

If your data is in cell B3 put the following formula in C3

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,"(",""),")","")," ","")

enter image description here

Note that Excel on MS Windows uses list separator character as specified in Windows Control Panel --> Regional and Language --> Additional Settings --> List Separator. In case in your version of Windows it's set to ; (Semi Colon) instead of , (Comma) the formula would then become.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3;"(";"");")";"");" ";"")
  • Well, it works with ; instead of , of course. If you can change your answer it would be even better. Thanks – Eypros Nov 28 '17 at 8:56
  • @Eypros - Answer updated. Additionally mentioned about List Separator character and also have shown the formula with ; (Semi Colon) as list separator instead of , (Comma) – patkim Nov 28 '17 at 9:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.