Delete all text after character in cells

Hi

Thanks for looking. I have a data column with data validation drop down lists, the list shows 3 pieces of information for the user to select the right one, however when they select the right one I only want to display part of the text in the drop down list. I'm looking for a work change event to automate the process.

I have seen and tried many ways but can't seem to get anything to work, probably because my VBA knowledge is limited!

validation format is: OIL089---MA91173474---VCL15456656

So what I need is just OIL089 showing and everything after the 9 should be deleted, or from the 1st hyphen onwards if you prefer.

Any help you can offer would be appreciated, I'm getting nowhere.

Please provide your file with the data validation already set up then we can add the code. See yellow banner at the top of the page.

Hi, thanks for the response.

I should have explained myself clearer so apologies for that. The text shown above was just 1 line of the drop down list to show as an example, further examples shown below. What I need is everything from the first hyphen onwards deleted, so from the examples below I would just want OIL089, OIL1005 & OIL1028 left in the cell.


OIL089---TMA0116757---VCL1839696
OIL1005---HLCUTEM200100536---CCN20239
OIL1028---590280937---CCN20367

Try this mod...

Please Login or Register  to view this content.

Hi Croweater

It works! so thank you very much for that, it really is appreciated

As always there is a but, what I am working on I am hoping to use as a template, this particular one has all the refs beginning with 'OIL' however that 3 digit prefix changes on other spreadsheets which is why I was hoping to find a solution that removes all text after from the 1st hyphen, that way it would work on all spreadsheets regardless of the prefix.

Of course I can just change the code on each template which wouldn't be a massive issue but If it could be avoided that would be my preference.

Any help you can offer is appreciated but if your done with me now I understand, I am already happier than when I went to bed last night/this morning!

Stuart

So just remove the first 'IF' statement. This will leave everything to the left of the first hyphen regardless.

Please Login or Register  to view this content.

Hi,
you may try

Please Login or Register  to view this content.

Perfect, thank you so much.

Stuart

Also worked, so thank you very much.

I have no idea what the difference is between the 2 so not sure if one is better than the other but they both appear to produce the same result.

Thanks again

Hi many thanks, I have 2 working options above which I have implemented. Thanks for the response though, its appreciated.

Stuart