Remove certain characters from a row?

Guanjin Peter

Active Member
Joined
May 21, 2008
Messages
429
let's say row 2 has data that looks like:

apple (kg), apple (g), orange (kg), orange (g)

it is possible to remove the (kg) and (g) tags so that it'll become
apple, apple, orange, orange

using VB code?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
let's say row 2 has data that looks like:

apple (kg), apple (g), orange (kg), orange (g)

it is possible to remove the (kg) and (g) tags so that it'll become
apple, apple, orange, orange

using VB code?

Just a suggestion..

Why do you want to use VBA when you can do it using Find & Replace option in the Edit menu in Excel ?

Select your data, Press Ctrl + H --> In Find What option type "(kg)" without the quotes --> Leave the Replace with option blank and click on Replace All. Repaet the same steps with "(g)" without the quotes in the Find what option.
 
Upvote 0
How is this?

Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/06/2008 by Fazza
'
'
    Cells.Replace What:=" (*)", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub
 
Upvote 0
Above was just straight from the macro recorder. You can trim it, :)
Code:
Cells.Replace What:=" (*)", Replacement:="", LookAt:=xlPart
 
Upvote 0
Code:
Sub test()


        Cells.Select
        
        Selection.Replace What:="(kg)", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
        Selection.Replace What:="(g)", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False


End Sub
 
Upvote 0
w0w thank you so much!!

please forgive me for asking this noob question....
i'm using nPro to manipulate the number of columns to copy to...can help me with this?


Cells(R6C[" & nPro & "]:R6C[" & nPro & "]).Replace What:=" (*)", Replacement:="", LookAt:=xlPart


I think it's ok...Thanks so much
 
Last edited:
Upvote 0
maybe
Code:
range(Cells(6, nPro), cells(6,nPro)).Replace etc

But with nPro in twice, you could just use
Code:
cells(6,nPro).replace etc
 
Upvote 0
I learnt via a book and plenty of practice, Peter. Recommend the same for you.

Initially it is confusing. It takes a very long time to do even simple things. After a year or so though, coding will be much, much, much easier.

Regards, Fazza
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top