How to separate "123456" from below entity?
ABC CD xyzad,ABCDE - Agent 123456
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Separate word
0
Answers
0
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