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