Bank Reference Number

[Shabbo]

Dear Sir,
I have data like in column A and expected result in Column B.

 

[vletm]

Shabbo
You really should reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

as I have asked many times.

Eg below:
How to get the Best Results at Chandoo.org

• Use Relevant words in the Title and in the tag Box, This will aid future searches.
• When drafting a question, try and lay out the question in a clear and concise way.
• Try and tell the readers what is the situation, what have you tried and what do you want to achieve.
• Don't leave out information more info is better

 

[pecoflyer]

There is a logic in the three last examples, but not for A2?
Please explain the logic

 

[S. Das]

Dear Sir,
I have data like in column A and expected result in Column B.

I think this is not possible as the strings are different also the length of the Reference number is not unique.

 

[bosco_yip]

There is a logic in the three last examples, but not for A2? Please explain the logic

Hi pecoflyer,

The logic is to split the data with delimiter "-" or "/" and extract the 1st group of alphanumeric or numeric value. Please see my formula solution.

I think this is not possible as the strings are different also the length of the Reference number is not unique.

The "Reference Number" could be extracted by >>

In C2, formula copied down :

=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(MID(A2,LOOKUP(99,FIND("/",SUBSTITUTE(LEFT(A2,MIN(FIND({0,1,2,3,4}+{0;5},A2&1/17))),"-","/"),ROW($1:$99)))+1,99),"-","/"),"/",REPT(" ",99)),99))



Or,

You could use this shorter formula, but it returned numeric value and removed all leading 0 >>

=FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(A2,"-","/"),"/","</b><b>")&"</b></a>","//b[translate(.,'1234567890','')!=.][1]")