Excel: Find Function starting from the end of the text 3

[Hosacans]

Hi All,

I have a column of text, and i need to shorten them by locating the last ":" and taking all the text behind it.

my thoughts lead me to use a combination of right() and find(). but the problem is that there are several ":" in each text. and find() only looks for the first one from the beginning/left of the text. how do i make it to start of the end/right?

please advise.

sample data
Input:
2007R022RQ2F30:698:M:698:33903:03BL CM
Output:
03BL CM

Thanks
Hosacans

 

[AnotherHiggins]

You'll need to know the following to achieve your goal:
- How many times to ":" appear?
- How many characters are to the right of the final ":"?

First, let's figure out how many times ":" appears.
[tab]=LEN(A1)-LEN(SUBSTITUTE(A1, ":", ""))
will return 5. Len(A1) returns how many characters are in A1. From that total, we subtract the number of characters after replacing all ":"s with ""s.

Next, we'll replace the final : with a character that will never appear in your dataset. Two "pipes" in a row are usually safe. The pipe is the shifted character on the key above [Enter] - the same key as the slash (\).

[tab]=SUBSTITUTE(A1, ":","||", [green]LEN(A1) - LEN(SUBSTITUTE(A1, ":",""))[/green])
returns 2007R022RQ2F30:698:M:698:33903[red]||[/red]03BL CM

You'll notice we are using the first formula as the last argument to return which instance we want to replace.

Last step - get everything to the right of the last ":" (which we've replaced with a ||).

Everything put together looks like this:
[tab][COLOR=blue white]=RIGHT(A1, LEN(A1) - FIND("||", SUBSTITUTE(A1, ":", "||", LEN(A1) - LEN(SUBSTITUTE(A1, ":", "")))))[/color]

This will return 03BL CM

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[yogia]

Hi Hosacans:

In addition to the fine contribution from John, here is another way ...

  | A                                         B  
-------------------------------------------------------
1 | 2007R022RQ2F30:698:M:698:33903:03BL CM	03BL CM
2 |
3 |

array formula in cell B1 is ...

=RIGHT(A1,MATCH(":",MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1),0)-1)

This is to be entered with CTRL+SHIFT+ENTER rather than with just ENTER.

What I have done here is that I split the original string in the reversed order so that the last : in the original string will be the first : in the reveresed string.
Then I lfound the location of the desired : in the orginal string and then I used the RIGHT function to extract all characters following the desired :



Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[WinblowsME]

Here's the VBA way if you ever need it.

Sub Run_It()
   Debug.Print Strip_Last("2007R022RQ2F30:698:M:698:33903:03BL CM", ":")
End Sub

Private Function Strip_Last(curr_line As String, delimiter As String) As String
    Dim pos As Integer

    pos = [COLOR=red]InStrRev[/color](curr_line, delimiter)
    
    If pos = Len(curr_line) Then
       Strip_Last = Mid(curr_line, pos)
    Else
       Strip_Last = Mid(curr_line, pos + 1)
    End If
End Function

 

[AnotherHiggins]

Hosacans,

Did this work for you? Are you still having trouble?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Hosacans]

Hi John,

Yes this is working great! once you've shown me the way to format these IDs. All the implementations after that went smoothly.

THanks so much! and sorry for not updating the results.

I've also looked into WinblowsME's way for doing it in Access VBA so i can bypass using excel to format the data.

Thank you all for you technical inputs, it's really helpful

Hosacans

 

[AnotherHiggins]

No problem. I'm glad you got it sorted out!


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.