macro to format cell values

[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,

[ragulduy]

Please Login or Register  to view this content.

Made a couple assumptions about your data but I think it works for the examples given.

[narendrabr]

Please provide entire vba code not just the function.

Thanks in advance

[ragulduy]

Please Login or Register  to view this content.

You can also use it as a worksheet function.

Note - if you need a full working solution straight off the bat, then you need to include an idea of what your data looks like/what it is laid out like. I'm not going to be able to use telepathy to work out the full vba code you need to apply the function to your data.

[narendrabr]

Ok.Thanks.

Can you modify this code to my criteria?

mployee 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.

Please Login or Register  to view this content.

Thanks,

[ragulduy]

Please Login or Register  to view this content.

[narendrabr]

Can you help me to modify my above code?

Thanks

[narendrabr]

Can you please modify this code?

Please Login or Register  to view this content.

Thanks

[ragulduy]

Hi - I did, perhaps somebody else can you provide you a more suitable solution.

[FDibbins]

narendrabr you have been given 3 suggested answer already, and have not once indicated why or how they did not work, all you do is keep posting the same info and request.

To get the help you want, you will need to help those trying to help you. Also, please do not post duplicate threads, they will be closed.

yudlugar has offered some VBA suggestions, here is a regular formula, based on your data, that you can add to a helper column and copy down...
=IF(OR(ISNUMBER(FIND("to",A2,1)),ISNUMBER(FIND(">",A2,1))),RIGHT(A2,3),IF(OR(ISNUMBER(FIND("+",A2,1)),ISNUMBER(FIND(",",A2,1))),LEFT(A2,3),RIGHT(A2,4)))

[narendrabr]

Hi,

Can you please provide the macro instead of the formula?

Thank!

[millz]

I find this thread humourous, especially

Please provide entire vba code not just the function.

@narendrabr, do you have any idea what you are doing? (this is a sincere question, not meant to be offensive)

In this closed thread, I've also provided a solution which seems to cover what you described. But you replied with a 'can you please provide the code?'. The code is in the file, just look into the VB editor?

[narendrabr]

Hi,

Sorry for that.I do not see any attached file in your reply.

Thanks in advance!

[narendrabr]

Hi,

The code does not have Sub and end sub.
I am basically looking to modify my code posted in #8

Thanks!