Remove certain characters from a row?

[Guanjin Peter]

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?

 

[Stormseed]

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.

 

[Guanjin Peter]

for convenience...at the click of a button...remove the tags...

 

[Fazza]

How is this?

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

 

[Fazza]

Above was just straight from the macro recorder. You can trim it,

Cells.Replace What:=" (*)", Replacement:="", LookAt:=xlPart

 

[Stormseed]

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

 

[Guanjin Peter]

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

 

[Fazza]

maybe

range(Cells(6, nPro), cells(6,nPro)).Replace etc

But with nPro in twice, you could just use

cells(6,nPro).replace etc

 

[Guanjin Peter]

w0w...amazing...what's really the diff between cells and range....very confusing...

 

[Fazza]

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