PDA

View Full Version : Solved: Removing specific characters from a cell



lanhao
10-18-2006, 11:28 AM
This may be a weird or off question, I have a bunch of cells that need to have the hyphen removed from them for sort purposes.

The number format is 000000-00 right now, is there a macro/command i can tell the computer to just go through and remove the hyphen?

Thanks for any suggestions/help on this.

Charlize
10-18-2006, 11:33 AM
Is the format a mask or is it a hardcoded hyphen ? I mean, input 000000 becomes 0000-00 or you had to input 0000-00 to get 0000-00.

Charlize

Simon Lloyd
10-18-2006, 12:03 PM
Hi just recorded this, so it should work if you assign a button to it

Sub DelIt()
Cells.Replace What:="-", Replacement:="", LookAt:=xlPart, SearchOrder:= _xlByRows, MatchCase:=False, SearchFormat:=False,ReplaceFormat:=False
End Sub
Regards,
Simon

lanhao
10-18-2006, 12:09 PM
it's specifically something that was manually entered... it should not have been put in, but some people don't listen to instrucoitns at this end :P

i set up a custom format for it to automatically put it in, but it's being a pain when trying to sort and compare information. That's what needs to be fixed. If it were a small amount, i would have no problem doing it, but I'm looking at over 26 k in rows of data.

Cyberdude
10-18-2006, 12:27 PM
It seems to me that the macro that is doing the sorting will have to reformat the list of values, then sort, then format them back to the original. Shouldn't be difficult.

lanhao
10-18-2006, 12:35 PM
Thanks much - it worked great... don't understand why it was being a royal pain in the you know what... wait, i think i am stillr ecovering from braindrain from some earlier mishaps other people did to my stuff.

^^;

Thank you so much again. Hope I can return the favor at some point in time. :)