To ignore letters in cell during our calculations

[arungowda4]

Hello All,

I need your help to write excel formula to one of my business requirement. Below is the question for which i am finding solution. Please spare a moment to help me out on this as i have limited excel knowledge.

We receive data everyday in a excel and at times our data vendors send invalid text in a excel cell which is not required and we need to ignore that, hence i need your help to write a excel formula to skip certain characters in the cell.

Example:
Nov13/Dec13 176.00 +0.00 Correct

LHNov13/FHDec13 178.00 +0.00 Wrong


We already have formula to get months like Nov13/Dec13 and ignore the rest, we are using =MID(B1,1,23) this formula to get result of Nov13/Dec13. But this formula doesnt work when our data provider sends data like LHNov13/FHDec13.

Hence, we need to ignore LH & FH and other values and should get only Nov13/Dec13.
So could you please tell me as how it can be done through excel formula?

Thankyou for your help
Regards,
Arun

 

[SkipVought]

Hi,

Unless you know with absolute certainty, what variations might infect this text, and incorporate the logic describing those variations into the formula or process, it will not be possible.

You describe one varant

Nov13/Dec13 176.00 +0.00 Correct

LHNov13/FHDec13 178.00 +0.00 Wrong

Where the required text is
[tt]
mmmdd/mmmdd nnn.nn +n.nn
[/yy]
And the variant is ALWAYS
[tt]
XXmmmdd/XXmmmddd nnn.nn +n.nn
[/tt]

Is that correct?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[byundt]

Assuming the only two variants are as shown, you might search for the 5 characters before "/" and then the five characters before the subsequent space:
=MID(A1,FIND("/",A1)-5,6) & MID(A1,FIND(" ",A1,FIND("/",A1))-5,5)

If the data vendor is even more diabolical than shown, then you will probably want to use a user-defined function with regular expressions.

 

[byundt]

Using regular expressions, you can identify the dates in diabolical test strings and return Nov13/Dec13 for each case. The test strings below mix up month abbreviations with full text, spaces between month and year, and two & four digit years.
Nov13/Dec13 176.00 +0.00 Correct
Nov 13/Dec 2013 176.00 +0.00 Correct
LHNov13/FHDec13 178.00 +0.00 Wrong
LHNovember 2013/FHDecember 2013 178.00 +0.00 Wrong

You use the regular expressions with a user-defined function. Install the user-defined function just like a macro in a regular module sheet. Use the function wizard (fx icon to left of formula bar) the first time (user-defined functions are the last choice in the dropdown) to preserve capitalization. You will end up with a worksheet formula like:
=DateFinder(A1)

Function DateFinder(sInputText As String) As String
Dim RegExp As Object, oMatch As Object, oMatches As Object
Dim s1 As String, s2 As String
DateFinder = ""
Set RegExp = CreateObject("VBScript.RegExp")
With RegExp
    .Pattern = "(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)([a-z]*)(\s*)(\d{2,4})"
    .Global = True
    .IgnoreCase = True
    If .test(sInputText) Then
        Set oMatches = .Execute(sInputText)
        If oMatches.Count > 1 Then
            s1 = .Replace(oMatches(0), "$1$4")
            s2 = .Replace(oMatches(1), "$1$4")
            s1 = Left(s1, 3) & Right(s1, 2)
            s2 = Left(s2, 3) & Right(s2, 2)
            DateFinder = s1 & "/" & s2
        End If
    End If
End With
Set oMatches = Nothing
Set RegExp = Nothing
End Function

 

[xlhelp]

Also, please look at this link


Avoid Hangovers. Stay drunk.