• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract Number reference

ranjit1964

New Member
I have data from two sources and i need to extract Reference 17636225 from each to adjacent column

Hope to hear

source 1
SB-TELEGRAPHIC TRF SBN82778429 SB REF. 17636225,ER51919 FAVG. BLUE BIRF FUND TRANSFER FROM AVN TO APR Value DATE, 12/06/2019 - NE71265

SOURCE-2
AVN-17636225 FUND TRANSFER FROM AVN TO APR DHS 1,100,000 VALUE DT 12/06/2019
regards
jitne
 
ranjit1964
as You seems to know Your reference (17636225) then
=substitute(your_text,17636225,"")
and You could write it (17636225) to needed place (or use it with that formula).
 
Last edited:
62321

Formula solution for Excel 2013 or above.

To extract 8 digits number in a string

In B2, copied down :

=FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,",","-"),"-"," ")," ","</b><b>")&"</b></a>","//b[.>9999999][.<100000000]")

Regards
Bosco
 
ranjit1964
as You seems to know Your reference (17636225) then
=substitute(your_text,17636225,"")
and You could write it (17636225) to needed place (or use it with that formula).
Please give more information and example for your source data.

Regards
Bosco

Hi Bosco
Please find attached excel sheet
Regards
Ranjit
 

Attachments

  • excel query.xlsx
    8.8 KB · Views: 3
Last edited by a moderator:
Hi Bosco
Please find attached excel sheet
Regards
Ranjit
Hi,

In your attached file, your source data contain HTML non-breaking spaces CHAR(160), and should replace them with regular spaces.

So,

the formula need add one more SUBSTITUTE () for the replacement and revised as in :

=FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4,CHAR(160)," "),",","-"),"-"," ")," ","</b><b>")&"</b></a>","//b[.>9999999][.<100000000]")

Please see my attached file

Edit : Should your source data spaces all filled with CHAR(160), the formula can be shortened in :

=FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4,",","-"),"-",CHAR(160)),CHAR(160),"</b><b>")&"</b></a>","//b[.>9999999][.<100000000]")

Regards
Bosco
 

Attachments

  • excel query(BY).xlsx
    10.3 KB · Views: 8
Last edited:
Hi,

In your attached file, your source data contain HTML non-breaking spaces CHAR(160), and should replace them with regular spaces.

So,

the formula need add one more SUBSTITUTE () for the replacement and revised as in :

=FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4,CHAR(160)," "),",","-"),"-"," ")," ","</b><b>")&"</b></a>","//b[.>9999999][.<100000000]")

Please see my attached file

Edit : Should your source data spaces all filled with CHAR(160), the formula can be shortened in :

=FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4,",","-"),"-",CHAR(160)),CHAR(160),"</b><b>")&"</b></a>","//b[.>9999999][.<100000000]")

Regards
Bosco
Thank you it works
 
Back
Top