Extract Text and Number from Column and Separate in Column B

[francisobrien2011]

Hello,

I need to extract Invoice numbers from a raw dataset below. I need to find all numbers after the "Inv" text string and put the three numbers associated with the invoice in the next column. But data has some strange formatting issues. How do I do this in excel using a formula or macro?

Best,

Francis

Example Raw Data Format

Athfd bacds Inv508 Farmer
Ex+ Athfdgg-icecream Inv408
Ath EX+ Inv403Cui
BIOCANTON 1373 EXP INV449
BMC 100461893 EXP INV401A
Ath EX+ Inv405 Milk
Athfdgg EM service Inv426-Gong
BIOCANTON 1325 EXP INV450
BMC 100462168 EXP INV400A
BMC 100462169 EXP INV409-421
BMC 100462996 EXP INV399
Ath Ex+ Inv451 Bais 7/31/2015
Ath EX+ bacds Inv501 oats
BMC 100469144 EXP INVS 502-504
EX+ Athfdgg Inv513-Mizgerd




Ath EX+ bacds Inv507 Trackman
Ath Ex+ Serv. Inv505 icecream
Athfd bacds Inv508 Farmer


Ex+ Athfdgg-icecream Inv408
Ex+ Athfdgg-icecream Inv432
Ath EX+ Inv403Cui

Ath Ex+ Inv407 Perissi
Ath Ex+ Inv430 Farmer
Ath Ex+ Inv431 Perissi
Ath Ex+ Inv446 Kirsch
BIOCANTON 1373 EXP INV449
BMC 100461893 EXP INV401A
Ath EX+ Inv405 Milk

Ath Ex+ Inv436 Walsh

Athfdgg EM service Inv426-Gong
BIOCANTON 1325 EXP INV450
BMC 100462168 EXP INV400A
BMC 100462175 INV424
BMC 100462169 EXP INV409-421
BMC 100462996 EXP INV399
Ath Ex+ Inv451 Bais 7/31/2015
Ath Ex+ Inv454 Farmer 8/5/15
Ath Ex+ Inv456 Mochida 8/27/2015
Ath Ex+ Inv457 Chenka 8/10/15
Ath Ex+ Inv458 icecream 8/3/15
Ath Ex+ Inv459 Sherman 7/30/15
Ath Ex+ Inv460 Sherman 8/3/15
Ath Ex+ Inv461Singh 713/2015
Ath Ex+ Inv462 Walsh 8/7/2015
Ath Ex+ Inv467 Sahin-Toth 9/16/2015
Ath Ex+ Inv468 Mizgerd 8/31/2015
Ath Ex+ Inv469 Mochida 9/23/2015
Ath Ex+ Inv471 Rahimi 8/5/15
Ath Ex+ Inv472Rahimi 8/24/15
Ath Ex+ Inv473 icecream 7/31/2015
Ath Ex+ Inv474 icecream 9/21/2015
Ath Ex+ Inv475 Thiagalingam 9/25/2015
Ath Ex+ Inv476 Mizgerd 10/13/2015
Ath Ex+ Inv477 Sahin-Toth 10/16/2015
Ath Ex+ Inv478 Trackman 10/13/15
Ath Ex+ Inv466 Denis 9/18/2015
Ath Ex+ Inv479 Godfrey
Ath EX+ Inv493 icecream
Ath EX+ Inv494 icecream
Ath Ex+ Inv490 Denis
BMC 100469144 EXP INV480-489
Ath EX+ bacds Inv501 oats
Ath EX+ bacds Inv506 Seshadri
BMC 100469144 EXP INVS 502-504
EX+ Athfdgg Inv513-Mizgerd
EX+ Athfdgg Inv514-Mochida
EX+ Athfdgg Inv524-Mochida
EX+ Athfdgg Inv525-Mochida
Ex+ Athfdgg Inv527 - Sahin-Toth
Ex+ Athfdgg Inv528 - Sahin-Toth
Ex+ Athfdgg Inv529 - Walsh
Ex+ Athfdgg Inv530 - Quinton
Ex+ Athfdgg Inv531 - Sahin-Toth
Ex+ Athfdgg Inv522 - Gokce
Ex+ Athfdgg Inv526 - Perissi

 

[Shteven]

If your data starts in A1, this should work for most:

=IFERROR(MID(A1,SEARCH("invs ",A1),8),IFERROR(MID(A1,SEARCH("invs",A1),6),IFERROR(MID(A1,SEARCH("inv ",A1),7),MID(A1,SEARCH("inv",A1),6))))

However, what about something like INVS 502-504. What do you want to see here?

 

[Nebu]

Hi:

I have created a UDF to achieve this.
Use the formula in the order I have used. I have also find and replace all INVS with INV to remove "S" and extra space in between.

Thanks

 

[bosco_yip]

Another formula option,

Return like Inv508, INV409-421, INVS502-504 or error control.…..

B1, formula copy down :

=MID(SUBSTITUTE(A1," ",""),SEARCH("inv",SUBSTITUTE(A1," ","")&"inv"),IF(ISNUMBER(-MID(A1,SEARCH("-",A1)+1,1)),11,6))

Regards
Bosco

 

[francisobrien2011]

If your data starts in A1, this should work for most:

=IFERROR(MID(A1,SEARCH("invs ",A1),8),IFERROR(MID(A1,SEARCH("invs",A1),6),IFERROR(MID(A1,SEARCH("inv ",A1),7),MID(A1,SEARCH("inv",A1),6))))

However, what about something like INVS 502-504. What do you want to see here?

Shteven,

I think it 502 - 504 means its inv 502, 503, 504. So not sure how to handle that and I may have to deal with it manually.

 

[francisobrien2011]

Hi everyone,

Thank you for the help on this question. The only tweak I need is for the Inv no.s to be in a separate column so I can do analysis on the numbers. But what you have done so far is amazing.

Best,

Frank

 

[bosco_yip]

Hi everyone,
Thank you for the help on this question. The only tweak I need is for the Inv no.s to be in a separate column so I can do analysis on the numbers. ......
Frank

Extract invoice nos. and split to separate columns.

In B1, enter formula :

=IFERROR(IF(ISNUMBER(-MID($A1,FIND("-",$A1)+1,1)),IF(COLUMN(A$1)<=MID($A1,FIND("-",$A1)+1,3)-MID($A1,FIND("-",$A1)-3,3)+1,MID($A1,FIND("-",$A1)-3,3)+COLUMN(A$1)-1,""),--MID($A1,SEARCH("inv",A1)+3,3)),"")

and, >> Custom Cell Format >> in the Type box, enter : "Inv "0

All copy across and down

Regards

 

[arbind gaba]

Formula which provide Invoice number as per data in question:

=IFERROR(LEFT(TRIM(REPLACE(A1,1,SEARCH("INVS",A1)+3,"")),3),LEFT(TRIM(REPLACE(A1,1,SEARCH("INV",A1)+2,"")),3))