Delete the numbers following the pound symbol from text string. How do you tell Excel 2007 that you want to identify any set of numbers following the # symbol and delete those numbers? Sometimes 2 numbers are following the text some time 3 numbers and the numbers are different. There are several rows and sheets with the bad data in it. I have tried text to colums and concatenating the rows back together a night mare. Lion, John;#504

Clem, Saul;#41;#Hall, Lynn;#33;#Miss, Tam;#295;#Montan, Marci E;#28;#Morgan, An;#290;#Ram, Rick;#29;#Rand, Shel;#21;#Hernan, Lu;#27

Try this: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"#",""),1,),2,),3,),4,),5,),6,),7,),8,),9,),0,),";;",";")

It is very long, so you might want to Copy/Paste from here rather than trying to re-enter it.

MIKE

Hooray!!! Thank you so much, I have been trying to solve this issue for days! You're the best!!!

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History