Deleting Words Based on Number of Words

[madhuchelliah]

Hello Folks, I want to delete few words in A2 cell of all the sheets in my WB. Please go through the 4 strings and their expected results. I want to keep the last numeric value of the string and if the string contains Alphabet as last word it should keep both numeric value and alphabet also.The string always has minimum of 2 and maximum of 4 words. I tried with formulas it doesn't work for all the 4 scenarios at same time. It seems some mathematical coding is required. Anyone have solution or suggestion please heads up. Thank you.

[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD]Actual
[/TD]
[TD]Expected
[/TD]
[/TR]
[TR]
[TD]21 C 220
[/TD]
[TD]220
[/TD]
[/TR]
[TR]
[TD]21 C 220 B
[/TD]
[TD]220 B
[/TD]
[/TR]
[TR]
[TD]21 220 B
[/TD]
[TD]220 B
[/TD]
[/TR]
[TR]
[TD]21 220
[/TD]
[TD]220
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 157"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[István Hirsch]

Try this:

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),(1+(RIGHT(A2,1)>="A"))*50))

 

[shuklaankur281190]

Try this:

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),(1+(RIGHT(A2,1)>="A"))*50))

Can you please explain this part (1+(RIGHT(A2,1)>="A"))*50))

 

[madhuchelliah]

Hello Istvan, It working great. If it is in vba format it will be good. Thank you.

 

[István Hirsch]

Can you please explain this part (1+(RIGHT(A2,1)>="A"))*50))

(RIGHT(a2,1)>="A") is TRUE (or 1) if the last character of the string is a letter, otherwise zero. So, in the former case 1+(right(a2,1)>="A") is 2, otherwise 1. It means that we go back from the right 2*50, or 1*50 characters, that involves the last two, or the last one substring, resp.

 

[István Hirsch]

Hello Istvan, It working great. If it is in vba format it will be good. Thank you.

Not very complex in VBA either, but I l leave it to somebody else.

 

[mikerickson]

Here is a UDF that can be used in VBA or as a worksheet function.

Function LastNumeralPlus(aString As String)
    Dim arrNum(0 To 9) As Long
    Dim i As Long
    For i = 0 To 9
        arrNum(i) = InStrRev(aString, " " & i)
    Next i
    LastNumeralPlus = Mid(aString, WorksheetFunction.Max(arrNum) + 1)
End Function

 

[madhuchelliah]

Here is a UDF that can be used in VBA or as a worksheet function.

Function LastNumeralPlus(aString As String)
    Dim arrNum(0 To 9) As Long
    Dim i As Long
    For i = 0 To 9
        arrNum(i) = InStrRev(aString, " " & i)
    Next i
    LastNumeralPlus = Mid(aString, WorksheetFunction.Max(arrNum) + 1)
End Function

Hello Mike, UDF is working great. But if my numerical value has -(minus) symbol along with alphabet it is not working. If numerical value has -(minus) symbol without alphabet it is working. Any solution for that?

 

[mikerickson]

Could you give a couple of examples of the strings that are giving you problems?

Are the "-" always associated with a number? Might "ABC -20 cat 3 -xyz" be an input string.

You might try

Function LastNumeralPlus(aString As String)
    Dim arrNum(0 To 10) As Long
    Dim i As Long
    For i = 0 To 9
        arrNum(i) = InStrRev(aString, " " & i)
    Next i
    arrNum(10) = InStrRev(aString, " -")
    LastNumeralPlus = Mid(aString, WorksheetFunction.Max(arrNum) + 1)
End Function

 

[mikerickson]

Or

Function LastNumeralPlus(aString As String)
    Dim arrNum(0 To 9) As Long
    Dim i As Long
    For i = 0 To 9
        arrNum(i) = WorksheetFunction.Max(InStrRev(aString, " " & i), InStrRev(aString, " -" & i))
    Next i
    LastNumeralPlus = Mid(aString, WorksheetFunction.Max(arrNum) + 1)
End Function