CODEWITHSUNDEEP
excelexcel-formula
Moonis Ahmed
Moonis Ahmed

Reputation: 5

Excel - pick specific characters from a string after a number

I have a list of strings where I want to split the numbers and alphabets part. For e.g. in cell A1 I have "FNN-12345 - Sample Text - 2016_AA1.1" (without the quotes ""). I want to split it to get just "Sample Text - 2016_AA1.1".

Appreciate any guidance on the formula.

Cheers.

Upvotes: 0

Views: 426

Answers (3)

MarianD
MarianD

Reputation: 14131

If the text which you want to select begin always on the position 13 (as in your example), use the formula

    =RIGHT(A1,LEN(A2)-12)

(supposing your original text is in the cell A1).

If you recognize the start of text by the pattern " - ", use the formula

    =RIGHT(A3,LEN(A3) -FIND(" - ",A4)-LEN(" - ")+ 1)

Upvotes: 0

Mladen Savic
Mladen Savic

Reputation: 201

This is the universal solution, no matter what the first alphanumeric string is:

=RIGHT(A1,LEN(A1)-FIND(" - ",A1)-2)

It finds the first occurence of the string " - " and keeps only the part after that string.

Upvotes: 1

Pierre Chevallier
Pierre Chevallier

Reputation: 754

You can use the functions to manipulate strings of characters in Excel like Left, Right or Mid to get the desired result in combination with a Lenghth function.

As such for you result you could try :

=RIGHT(A1, LEN(A1) - LEN("FNN-12345 - "))

This formula would take the length of the entire cell and remove the FNN-12345 - part. Of course you can add a column which contain the desired elements to be removed.

Upvotes: 0

Related Questions