Need macro to find and replace

[narendrabr]

Hi,

I have to clean data like as mentioned below on regular basis.I need macro to find and replace the values quickly.

Employee Count
1000-9999
1000-9999
1000-9999
1000-9999
1000-9999
100+
100,

I have to keep all the data after hyphen that is 9999
and remove trailing + and commas all in one function.
The macro should help me to clean these three requirements.
Thanks,

[Solus Rankin]

Please Login or Register  to view this content.

[stnkynts]

Please Login or Register  to view this content.

[narendrabr]

Hi Can you please include two more criteria to the code.

Sub RunMe()
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
Dim rCell As Range
Dim mySplit

For Each rCell In ws.UsedRange.SpecialCells(xlCellTypeConstants)
If Not InStr(1, rCell.Value, "-") = 0 Then
mySplit = Split(rCell.Value, "-")
rCell.Value = mySplit(UBound(mySplit))
Else
If Right(rCell.Value, 1) = "+" Or Right(rCell.Value, 1) = "," Then
rCell.Value = Mid(rCell.Value, 1, rCell.Characters.Count - 1)
End If
End If
Next rCell

End Sub
Employee Count
100 to 200
100>500

Thanks

[narendrabr]

Hi,

Thanks for help.Actually I need to add two more criteria to the above conditions.
Employee Count
100 to 200
100>500

I have to keep all the data after to that is 200
I have to keep all the data after > that is 500

Thanks

[stnkynts]

I have no idea what you mean

[narendrabr]

Hi,

I have to clean data like as mentioned below on regular basis.I need macro to find and replace the values quickly.

Employee Count
1000-9999
1000-9999
1000-9999
1000-9999
1000-9999
100 to 200
100>200
100+
100,

I have to keep all the data after hyphen that is 9999
I have to keep all the data after 'to' that is 200
I have to keep all the data after '>' that is 200
and remove trailing + and commas all in one function.
The macro should help me to clean these requirements.
Thanks,

[jindon]

Try

Please Login or Register  to view this content.

[stnkynts]

Here you go

Please Login or Register  to view this content.

[jaslake]

Here's another

Please Login or Register  to view this content.

[narendrabr]

Hi,

I have to create the Data check bar in tool bar section and under Data check bar I need to create ribbon called find and replace for this code.

Please provide step by step instruction design this tool.

Please Login or Register  to view this content.

[narendrabr]

Hi,

I have to create the Data check Tab and under Data check bar I need to create ribbon button called find and replace for this code.

Please provide step by step instruction design this tool.

Please Login or Register  to view this content.

[narendrabr]

Hi,

I have to clean data like as mentioned below on regular basis.I need macro to find and replace the values quickly.

Employee Count
1000-9999
1000-9999
1000-9999
1000-9999
1000-9999
100 to 200
100>200
100+
100,
Over 1000
I have to keep all the data after hyphen that is 9999
I have to keep all the data after 'to' that is 200
I have to keep all the data after '>' that is 200
I have to keep the data after 'Over'
and remove trailing + and commas all in one function.
The macro should help me to clean these requirements.
Thanks,

[narendrabr]

I have to add one more criteria to this code.I have to retain
Only 1000 if the data has over 100 or any.

Please Login or Register  to view this content.

[narendrabr]

Please find attached sheet.

[narendrabr]

Hi,

I have to format data data like as mentioned below on regular basis.I need macro to find and replace the values quickly.

Employee Count
1000-9999
1000-9999
1000-9999
1000-9999
1000-9999
100 to 200
100>200
100+
100,
Over 1000
I have to keep all the data after hyphen that is 9999
I have to keep all the data after 'to' that is 200
I have to keep all the data after '>' that is 200
I have to keep data after Over
and remove trailing + and commas all in one function.
The macro should help me to clean these requirements.
Thanks,

[narendrabr]

Hi,

I have to format data data like as mentioned below on regular basis.I need macro to find and replace the values quickly.

Employee Count
1000-9999
1000-9999
1000-9999
1000-9999
1000-9999
100 to 200
100>200
100+
100,

I have to keep all the data after hyphen that is 9999
I have to keep all the data after 'to' that is 200
I have to keep all the data after '>' that is 200
and remove trailing + and commas all in one function.
The macro should help me to clean these requirements.
Thanks,

[narendrabr]

Hi Basically if I run a macro it should quickly remove any unwanted data like shown below
The macro can be runned to any column not just the column A.
Employee Count
1000-9999
1000-9999
1000-9999
1000-9999
1000-9999
100 to 200
100>200
100+
100,
Over 1000

I have to keep all the data after hyphen that is 9999
I have to keep all the data after 'to' that is 200
I have to keep all the data after '>' that is 200
I have to keep all the data after Over
and remove trailing + and commas all in one function.
The macro should help me to clean these requirements.
Let me know if you need any clarification

Thanks!