The simplest formula solution that caters for numbers of any length applies for the case of a fixed count of colon-delimited numbers at the start of the data, e.g. for three numbers:
Enter the following formula in B2
and ctrl-enter/copy-paste/fill-down/auto-fill into the rest of the table's column:
=RIGHT(A2,LEN(A2)-FIND(CHAR(1),SUBSTITUTE(A2,":",CHAR(1),3)))
Explanation:
The optional fourth argument to SUBSTITUTE()
allows us to substitute a specific occurrence of the :
character, in this case the third, with a special character that doesn't occur elsewhere in the text. CHAR(1)
is used as it is pretty much guaranteed to never occur in any string.
This results in a second solitary separator where the data we wish to keep is to the right. Your RIGHT()
formula will now work correctly.
Note that this solution will also work even if there are colons in the text you wish to keep.
A more general formula solution, that allows for a variable count of :
delimiters numbers at the start of the data (as well as numbers of any length), is:
=RIGHT(A2,LEN(A2)-FIND(CHAR(1),SUBSTITUTE(A2,":",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,":","")))))
Explanation:
The second SUBSTITUTE()
deletes all the colons. The difference between the length of the original text and the length of the text with the deleted colons is the count of the colons.
For the sample data:
LEN(A2)-LEN(SUBSTITUTE(A2,":",""))
→3
.
- Thus
=RIGHT(A2,LEN(A2)-FIND(CHAR(1),SUBSTITUTE(A2,":",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,":","")))))
→=RIGHT(A2,LEN(A2)-FIND(CHAR(1),SUBSTITUTE(A2,":",CHAR(1),3)))
i.e. the same as the first formula (except that for the first formula the 3
is the third colon, whereas in this one it is the last colon)
Obviously, this formula will not work correctly if there are colons in the text you wish to keep.
:
and delete everything up to that point?