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.

Improve this question

1 Answer 1

Reset to default
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;"(";"");")";"");" ";"")
Improve this answer
3
  • Well, it works with ; instead of , of course. If you can change your answer it would be even better. Thanks
    – Eypros
    Nov 28, 2017 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, 2017 at 9:06
  • Unfortunately, the upper limit of substitute functions to be used in one cell is 8. But what if you have more than 8 strings to substitute?
    – MsGISRocker
    Jul 7, 2021 at 15:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .