Help to return only first 12 characters when a cell contains specific text

**ports83** · 10-25-2012, 09:14 PM

Hi Guys,
I have a spreadsheet which has some unnecessary characters added onto the end of some records which I need to remove. I am essentially trying to ensure that if a Cell in column A begins with (or contains) "PR" that it only returns the first 12 characters. Other records in column A may remain longer than 12 characters if they don't begin with "PR".

I have written the below code based off of a number of different google searches and some of my very limited knowledge of VBA and it is successfully returning only 12 characters, but for everything, not just for records that begin with PR.

Is someone able work out where I've gone wrong and assist?

Please Login or Register  to view this content.

**FDibbins** · 10-25-2012, 09:37 PM

not sure whats wrong with your VBA, but if you want a formula, try this...

=IF(ISERROR(SEARCH("PR",A1,1)),A1,LEFT(A1,12))
i assumed your data is in A1, adjust as needed

**ports83** · 10-25-2012, 09:45 PM

I forgot to attach this earlier... this is the current spreadsheet I'm working with.

**Winon** · 10-25-2012, 09:47 PM

@ FDibbins,

Works perfect for me! Better tan VBA.

**FDibbins** · 10-25-2012, 09:56 PM

thanks Winon, sometimes simpler is better

**ports83** · 10-25-2012, 10:02 PM

This is actually one part of a larger code to try and automate the file from when I receive it to have it formatted to suit our system and then email out. How do I go about using your formula in VBA so I can add it to the rest of my code?

My working knowledge of VBA is pretty limited, everything I have written is based off of prior examples and stitched together...

**mike7952** · 10-25-2012, 10:14 PM

Give this a try

Please Login or Register  to view this content.

**ports83** · 10-25-2012, 10:30 PM

Mike,
That seems to give me exactly what I want, though I'm not completly sure what it is doing... but thats my limited knowledge there.
Thanks heaps!!