October 10, 2008 at 2:44 am
Hi Guys,
A bit off the topic but thought you might be able to help.
I have thousands of lines like below and I'm trying to clean them. I need to remove everything before and including the third dash (-). Please could someone give me an excel formula I can apply to all the cells to help achieve this.
I have given the original lines below and the desired state.
Thanks a lot!
Brad
ORIGINAL:
02-C05-00-000-OH Operations overheads
02-C05-160-000-Ops-Overheads-Pat Kleinhans
DESIRED:
000-OH Operations overheads
000-Ops-Overheads-Pat Kleinhans
October 10, 2008 at 3:09 am
Hi Brad,
This should work:
=MID(A1, FIND("-", A1, FIND("-", A1, FIND("-", A1, 1)+1)+1)+1, LEN(A1) - FIND("-", A1, FIND("-", A1, FIND("-", A1, 1)+1)+1))
There may be an easier way to do it
Oddly enough something like this turned up at work yesterday! So I had been looking up FIND in Excel, as data comes from Excel first, and we have to get the data right before importing into SQL (3rd party data import tool)
Roddders...
October 10, 2008 at 3:35 am
Thanks Rodders!!
November 20, 2008 at 4:36 am
Hi Roddders
Thank you for the formula.
Mathew
November 21, 2008 at 3:26 pm
I have done this a million different ways and I always seem to get a situation where my formula's are darn near impossible to understand months later. Excel has a nice feature that allows you to roll your own worksheet functions. With this code:
Function ParseString(Str_Pattern As String, Str_Field As String) As String
Dim int_StrIndex As Integer 'Integer to keep track of index position of string
Dim int_FindCount 'Tracks how many time a pattern is found
'For loop to run through each character in string until it finds 3 matches.
For int_StrIndex = 1 To Len(Str_Field)
'Test to see if character matches pattern
If Mid(Str_Field, int_StrIndex, 1) = Str_Pattern Then int_FindCount = int_FindCount + 1
If int_FindCount = 3 Then
'Have to increment counter 1 more position to get past the str_Pattern character
int_StrIndex = int_StrIndex + 1
Exit For
End If
Next
'Now that the string has been iterated through, if there was 3 matches return the parsed string
If int_FindCount = 3 Then
ParseString = Mid(Str_Field, int_StrIndex, Len(Str_Field) - int_StrIndex)
Else
'If it did NOT find it atleast 3 times just return the whole string
ParseString = Str_Field
End If
End Function
The formula in excel would look like this "=ParseString("-",A1")"
Works nicely. The one downside to this is that each time you sort the column or make changes to the VBA code, it will re-calc the formula(dont beat me up if im wrong on the last one).
Just open the VBA Editor (Alt+F11) insert a module in the workbook you are using, and paste this code into. You can take this a step further and make an add-in out of it and bingo, its available for all your workbooks.
If you would like help let me know its a simple process.
Please forgive the LIBERAL use of commenting. I automate excel quite a bit and always get asked how I did something, so when they go to look back through it they understand.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply