Hello All,
if I have a field mixed with text and invoice number, how to easily extract it as shown in this sample below:
how to find invoice number inside a text?
-
- 5StarLounger
- Posts: 1192
- Joined: 01 May 2016, 09:58
how to find invoice number inside a text?
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.
-
- 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?
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?
How do we know that 2443 is an invoice number but 5000 is not?
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 254
- Joined: 15 Aug 2016, 11:23
Re: how to find invoice number inside a text?
Hi,
I think he wants the serial number of the invoice number.
I think he wants the serial number of the invoice number.
-
- 3StarLounger
- Posts: 254
- Joined: 15 Aug 2016, 11:23
Re: how to find invoice number inside a text?
One way
In O3 then drag down:
=VLOOKUP("*"&N3&"*",$K$3:$L$7,2,0)
In O3 then drag down:
=VLOOKUP("*"&N3&"*",$K$3:$L$7,2,0)
-
- 5StarLounger
- Posts: 1192
- Joined: 01 May 2016, 09:58
Re: how to find invoice number inside a text?
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
-
- 5StarLounger
- Posts: 1192
- Joined: 01 May 2016, 09:58
Re: how to find invoice number inside a text?
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
-
- 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?
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
Hans
-
- 3StarLounger
- Posts: 254
- Joined: 15 Aug 2016, 11:23
Re: how to find invoice number inside a text?
Not just any number, but a number on the "INVOICE NUMBER" list. Or number containing number from invoice list.In this case if we have another number in the row before the invoice number, the function will take the first number
In your example, there is no such situation.
-
- 5StarLounger
- Posts: 1192
- Joined: 01 May 2016, 09:58
Re: how to find invoice number inside a text?
thanks for all, so we have to find a way not to let the users put the information this way.
Regards
Regards
-
- 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?
I'd use a separate column for the invoice number.
Best wishes,
Hans
Hans