how to find invoice number inside a text?

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

how to find invoice number inside a text?

Post by siamandm »

Hello All,

if I have a field mixed with text and invoice number, how to easily extract it as shown in this sample below:
Screenshot 2021-08-05 154715.jpg
You do not have the required permissions to view the files attached to this post.
Last edited by siamandm on 07 Aug 2021, 09:38, edited 1 time in total.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: how to find invoice number inside a text?

Post by HansV »

How do we know that 2963 is an invoice number but 20000 and 210407012373 are not?
How do we know that 2443 is an invoice number but 5000 is not?
Best wishes,
Hans

Toranaga
3StarLounger
Posts: 254
Joined: 15 Aug 2016, 11:23

Re: how to find invoice number inside a text?

Post by Toranaga »

Hi,

I think he wants the serial number of the invoice number.

Toranaga
3StarLounger
Posts: 254
Joined: 15 Aug 2016, 11:23

Re: how to find invoice number inside a text?

Post by Toranaga »

One way

In O3 then drag down:
=VLOOKUP("*"&N3&"*",$K$3:$L$7,2,0)

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: how to find invoice number inside a text?

Post by siamandm »

HansV wrote:
05 Aug 2021, 13:10
How do we know that 2963 is an invoice number but 20000 and 210407012373 are not?
How do we know that 2443 is an invoice number but 5000 is not?
If we have a list for the invoice numbers, and write a formula to find only those numbers in the list, and return the serial number does this will help?
Regards

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: how to find invoice number inside a text?

Post by siamandm »

Toranaga wrote:
05 Aug 2021, 14:41
One way

In O3 then drag down:
=VLOOKUP("*"&N3&"*",$K$3:$L$7,2,0)
Thank you for the reply,
In this case if we have another number in the row before the invoice number, the function will take the first number

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: how to find invoice number inside a text?

Post by HansV »

Toranaga's formula should do what you want. If it returns the wrong serial number, that would only happen if a remark contains two (or more) valid invoice numbers. We have no way to decide which one is correct...
Best wishes,
Hans

Toranaga
3StarLounger
Posts: 254
Joined: 15 Aug 2016, 11:23

Re: how to find invoice number inside a text?

Post by Toranaga »

In this case if we have another number in the row before the invoice number, the function will take the first number
Not just any number, but a number on the "INVOICE NUMBER" list. Or number containing number from invoice list.
In your example, there is no such situation.

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: how to find invoice number inside a text?

Post by siamandm »

thanks for all, so we have to find a way not to let the users put the information this way.

Regards

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: how to find invoice number inside a text?

Post by HansV »

I'd use a separate column for the invoice number.
Best wishes,
Hans