1

I would like to remove the numbers, including the colon, from each row.

Example data:

22:18:01(165):access-list OUTSIDE_IN extended permit deny IP

I tried using the formula

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

but it didn't work. It deleted all of 22:18:01(165):access-list since there is no space after the last colon.

2
  • You mention colon but not parentheses. It isn't clear what output you expect. Your example does not contain numbers and colons before a letter without any spaces, so should nothing be done with this example?
    – fixer1234
    Aug 18, 2018 at 7:36
  • why don't just look for the last : and delete everything up to that point?
    – phuclv
    Aug 24, 2018 at 17:06

2 Answers 2

1

Assuming all your rows look like your example row (specifically, there are no colons in the portion of the text you want to keep), you can just use Text to Columns for this, specifying : as the delimiter.

Select the column with your data, and click Text to Columns on the Data tab.

Choose Delimited on the first screen, and on the second screen, only check Other and enter a colon :

Hit finish and your data should now be split into 4 columns based on each colon. Just delete the first 3 columns and you're left with access-list OUTSIDE_IN extended permit deny IP


Alternatively, assuming the number of characters before access-list is the same on every row, you can use =RIGHT(A2,LEN(A2)-14)

1

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:

Worksheet Screenshot

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .