Solved Identify different numbers in a cell w/text and numbers

August 13, 2013 at 15:18:35
Specs: Windows XP
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


See More: Identify different numbers in a cell w/text and numbers

Report •


#1
August 13, 2013 at 15:45:54
✔ Best Answer
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

http://www.skeptic.com/


Report •

#2
August 13, 2013 at 16:29:58
Hooray!!! Thank you so much, I have been trying to solve this issue for days! You're the best!!!

Report •

Related Solutions


Ask Question