Separate word


How to separate "123456" from below entity?

ABC CD xyzad,ABCDE - Agent 123456



There are many ways to do that. Unfortunately, your question is too vague to allow a positive selection among them. Let's start with this formula.

=RIGHT(A1,LEN(A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1 & "0123456789"))+1)

The condition under which this formula works is that the number you are looking for is at the end of the string in A1. If there is no number at the end a null string will be returned (not zero - a string, not a number). Likewise, the returned number is a text string, not a number. Leading zeroes will be retained therefore.

If you wish to extract a number - one with which you can do calculations - just add *1 to the above formula. Leading zeroes will disappear.

If your string in A1 can't meet the requirements mentioned above another method may be employed. You would have to tell us more about the exact construction of the original string in A1.

I have taken guidance fromĀ ExcelJet


Answer the Question

You must create an account to use the forum. Create an Account or Login