Formula to remove all but the last of the same character in a cell

[James01]

Hi

I have data in cell A1 and A2 which looks like below

SECTOR - 11, HIRAN MAGRI, - 313001
MAIN BRANCH, 30-15-171 DABA GARDENS, NEAR SARASWATI PARK,-530020,

In cell A1 the number of hypens (-) are 2
In cell A2 the number of - are 3

What I am looking for is a formula which can remove all additional - except the last one. Therefore the result of the formula should be

SECTOR 11, HIRAN MAGRI, - 313001
MAIN BRANCH, 30 15 171 DABA GARDENS, NEAR SARASWATI PARK,-530020

Is this possible by using a formula

Thanks for the replies

 

[goofy78270]

I have not seen this ability using a formula, but you can do this via a macro.

 

[wigi]

I have not seen this ability using a formula, but you can do this via a macro.

Hmmm, am I a formula God like Domenic, then?

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-","§",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))),"-"," "),"§","-")

Wigi

 

[goofy78270]

wigi,

Can you explain this formula a little bit, as I am a little lost in it actions?

 

[wigi]

wigi,

Can you explain this formula a little bit, as I am a little lost in it actions?

Sure.

=LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))

This calculates the number of - in A1. Say, this is 3.

=SUBSTITUTE(A1,"-","§",3)

This takes A1 and substitutes (ONLY) the 3rd - into a §, a character that does not occur in the cells (assumption).

The last but one step, is in that resulting string, replace - with "" (so deleting -) We don't delete the 3rd - because there are only 2 - in the string (and 1 §)

Last step is to replace § with -

Hope this is now clear? If not, ask again please.

 

[James01]

Amazing !!!

Also the way you explained the formula is excellent.

Many Thanks

 

[goofy78270]

Hmmm, am I a formula God like Domenic, then?

I will have to give it to you. Even on a Friday afternoon, there is still something to be learned.

Great job and great explanation.

It appears that the replacement is replacing with a space though, maybe it is just the eyes going on a Friday afternoon.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-","§",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))),"-"," "),"§","-")

 

[wigi]

It was indeed a space, thans for catching that one.

 

[pgc01]

James, in your examples the strings end with a 6 digit number preceeded by a hyphen. Is it always like that?

 

[James01]

Hi

Not always, sometimes there is more data