PDA

View Full Version : How to remove excess / in excel?


joeysison
Mar 14, 2012, 11:07 AM
/baf usd 0/caf 10.51 % of bas,ers,opa,pss,stt//////ddf eur 30/dhc eur 195////////////opa usd 50////psi eur 5/pss usd 0/ser usd 9//

To become

Baf usd 0/caf 10.51 % of bas,ers,opa,pss,stt/ddf eur 30/dhc eur 195/opa usd 50/psi eur 5/pss usd 0/ser usd 9

JBeaucaire
Mar 14, 2012, 11:39 PM
If that string were in A1, then in B1:

=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"////", "/"),"///", "/"),"//", "/"), 2, LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"////", "/"),"///", "/"),"//", "/"))-2)