Replace function in Excel

[ravins23]

Hi i have a column in excel sheet with data like ab-34-50-121.
I want to make it ab3450121 i.e. i want to remove '-'. I have like 400 rows in that column.
Is this possible in excel 2003 to do this.
I would greatly appreciate any input on this

[VBA Noob]

Select the column or range. Press Ctrl + H > in the find box enter - and leave the replace box blank

VBA Noob

[Andy Pope]

Can you not use Replace, CTRL+H
Select the column first then CTRL+H to bring up the dialog.

Finding "-". Replace with nothing

[all4excel]

Ravi,

You can try this formula option, if you dont want to use Cntrl + H and Replace..

[ = REPLACE(REPLACE(REPLACE(B2,FIND("-",B2,1),1,),FIND("-",REPLACE(B2,FIND("-",B2,1),1,),1),1,),FIND("-",REPLACE(REPLACE(B2,FIND("-",B2,1),1,),FIND("-",REPLACE(B2,FIND("-",B2,1),1,),1),1,),1),1,) ]

Considering that your values are in B2 just paste this solution in C2.

Let me know if u need any more help

[VBA Noob]

Ravi,

You can try this formula option, if you dont want to use Cntrl + H and Replace..

[ =REPLACE(REPLACE(REPLACE(REPLACE(B2,FIND("-",B2,1),1,),FIND("-",REPLACE(B2,FIND("-",B2,1),1,),1),1,),FIND("-",REPLACE(REPLACE(B2,FIND("-",B2,1),1,),FIND("-",REPLACE(B2,FIND("-",B2,1),1,),1),1,),1),1,),FIND("-",REPLACE(B2,FIND("-",REPLACE(REPLACE(B2,FIND("-",B2,1),1,),FIND("-",REPLACE(B2,FIND("-",B2,1),1,),1),1,),1),1,),1),1,) ]

Considering that your values are in B2 just paste this solution in C2.

Let me know if u need any more help

Just use

=SUBSTITUTE(A1,"-","")

VBA Noob

[all4excel]

Just use


VBA Noob

Ravi,

I think Noobs formula is more efficient in terms of complexity and memory space...

Moreover it can help you trim the "-" to any extent..