Copying Text Right of Specific Character

I have an Excel sheet where the information in column A are basically fomatted as such.

a1b2c3d4:abcd:1234

My goal is to have Excel take the characters after the right most colon and put them in column B. In this case it would be 1234.
However the problem for me is that the information in column A are always somewhat different, for example these could be 5 different entries in column A along with what I want to be in Column B.

ab:cdef = cdef
abcdef:12345:abc = abc
123:a1b2c3:1a2b3c:abc:456 = 456
abc:def:123456789 = 123456789
abcd = abcd

Notice also is that in the last example, there are no colons, so what I want in column B is basically the info in A itself.

Does anyone know how this can be done?

Thanks.

Hi LevinTrueno,

Welcome to the forum.

Though you've posted this in the Worksheet Functions area, the only way I could think of was to write the following macro to do the job:

Please Login or Register  to view this content.

Just change the starting row (i.e. B2) to suit.

HTH

Robert

The following formula should do what you want:

Please Login or Register  to view this content.

If any of your cells contain underscores, you can change the underscores in the formula o a character that won't appear in the cell you are checking such as | (shift backslash)

Thanks everyone!

mbdct, could I ask you to do a quick break down for me of that function you shared? I'm trying to learn Excel on the fly.

Please Login or Register  to view this content.

Checks if there is a ":" in the cells. If there is no : the result is an error and the cell itself is returned.

Please Login or Register  to view this content.

calculates the number of :s in the cell

Please Login or Register  to view this content.

changes out the last : for an underscore ( _ ).

Please Login or Register  to view this content.

Finds the location of the underscore and adds 1 to that position to get the location of the first character after the underscore (or the last : that it had replaced)

The ,99) part of the MID() function makes sure the remainder of the cell is returned

Here's another formula approach for data in A1

=REPLACE(A1,1,LOOKUP(2^15,FIND(":",":"&A1,ROW(INDIRECT("1:"&LEN(A1)))))-1,"")

Thanks everyone!