how to fetch a reference number from a cell and post in another

[skf786]

hello,

my data is in column A. Each cell consists of a sentence which contains a 10 digit reference number as well somewhere. Format of the reference is ACXXXXXXXXXX where the last 8 digits are numbers. I would like the column B to return the reference number ACXXXXXXXXXX. Please note that the reference can be anywhere in the cell i.e. beginning, middle or end and at times even joint with other text without space.

thank you for your help

Khalid

 

[Scott T]

If the reference number is the only case of capitalized AC in the string then this should work.
If not then need for information.

Book1
	A	B
10	the ref number is AC12345678.	AC12345678
11	ref# AC12345687 for this row.	AC12345687
12	the account is 5678 ref AC87654321	AC87654321
13	AC65432178 is the number	AC65432178
Sheet3

Cell Formulas
Range	Formula
B10:B13	B10	=MID(A10,FIND("AC",A10),10)

Note this uses find since it is case sensitive so it will ignore other cases of ac like in account. the reference will always need to start with a capitalized AC.

 

[skf786]

Thank you Scott. This works well. Also, is it possible to pick only where the last 8 digits are numbers only, not alphabets?

 

[Scott T]

If you want to look at the number I think a UDF would be best. As you can see from below the IF statement can only handle one case of ac (in the string that is not the ref number). You would need to nest more if statements to account for more cases of ac in the string. The substitute is case sensitive so it will only work if the non ref number that has AC in caps. It can be wrapped in iserror so it does not return an value error.

Book1
	A	B	C
10	the ref number is AC12345678.	AC12345678	AC12345678
11	ref# AC12345687 for this row.	AC12345687	AC12345687
12	the account is 5678 ref AC87654321	AC87654321	AC87654321
13	AC65432178 is the number	AC65432178	AC65432178
14	the account is 876 for acer ac98765412	acer ac987	ac98765412
15	125 account go fad fasa AC32158746	AC32158746	AC32158746
16	this does not have a ref number	#VALUE!	not found
Sheet3

Cell Formulas
Range	Formula
B10:B16	B10	=IF(I10,MID(A10,G10,10),MID(SUBSTITUTE(A10,"ac","||",1),SEARCH("ac",SUBSTITUTE(A10,"ac","||",1)),10))
C10:C16	C10	=refnum(A10)

The UDF will return not found if there is no ref number. that can be changed to what you want.

Function refnum(rng As Range)

Dim ccount As Long
ccount = Len(rng)
For x = 1 To ccount
    If Mid(rng, x, 1) >= "0" And Mid(rng, x, 1) <= "9" And x > 2 Then
        If UCase(Mid(rng, x - 2, 2)) = "AC" Then
            refnum = Mid(rng, x - 2, 10)
            Exit For
        End If
    End If
Next x
If refnum = Empty Then refnum = "not found"
End Function