| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to extract only badge numbers from such a column?
Excel 2003 user.
I have 3 columns like this in A,B & C. 1/2/2010 #13837-SALARY PAID FOR JAN-10 120.00 1/2/2010 #13838-SALARY PAID FOR JAN-10 215.00 1/2/2010 TERMINATION SETTLEMENT # 11136 903.47 1/2/2010 TERMINATION SETTLEMENT # 12536 894.20 1/2/2010 TERMINATION SETTLEMENT # 13044 292.49 1/2/2010 TERMINATION SETTLEMENT # 13159 282.78 1/2/2010 TERMINATION SETTLEMENT # 12199 1,169.64 1/2/2010 TERMINATION SETTLEMENT # 11261 2,623.03 1/2/2010 TERMINATION SETTLEMENT # 13630 626.25 1/2/2010 TERMINATION SETTLEMENT # 11840 668.22 1/2/2010 TERMINATION SETTLEMENT # 10572 1,671.76 2/2/2010 TERMINATION SETTLEMENT # 13638 704.32 2/2/2010 TERMINATION SETTLEMENT # 13626 297.88 2/2/2010 TERMINATION SETTLEMENT # 13579 408.92 2/2/2010 TERMINATION SETTLEMENT # 13055 863.25 2/2/2010 TERMINATION SETTLEMENT # 13192 284.67 2/2/2010 TERMINATION SETTLEMENT # 13627 436.33 3/2/2010 TERMINATION SETTLEMENT # 13559 381.55 3/2/2010 TERMINATION SETTLEMENT # 12155 1,521.07 3/2/2010 TERMINATION SETTLEMENT # 13569 190.65 3/2/2010 TERMINATION SETTLEMENT # 13448 188.96 3/2/2010 TERMINATION SETTLEMENT # 13501 412.53 3/2/2010 TERMINATION SETTLEMENT # 13580 483.04 3/2/2010 TERMINATION SETTLEMENT # 13137 270.75 3/2/2010 TERMINATION SETTLEMENT # 13492 244.12 3/2/2010 TERMINATION SETTLEMENT # 13505 268.19 3/2/2010 TERMINATION SETTLEMENT # 13577 186.48 3/2/2010 TERMINATION SETTLEMENT # 13573 190.65 3/2/2010 TERMINATION SETTLEMENT # 13491 323.74 3/2/2010 TERMINATION SETTLEMENT # 13469 178.56 3/2/2010 TERMINATION SETTLEMENT # 13566 171.73 3/2/2010 TERMINATION SETTLEMENT # 13475 234.72 3/2/2010 TERMINATION SETTLEMENT # 13407 177.61 3/2/2010 TERMINATION SETTLEMENT # 13556 181.83 3/2/2010 TERMINATION SETTLEMENT # 13599 355.87 3/2/2010 TERMINATION SETTLEMENT # 10929 896.49 3/2/2010 TERMINATION SETTLEMENT # 13045 297.35 3/2/2010 TERMINATION SETTLEMENT # 12573 968.99 3/2/2010 TERMINATION SETTLEMENT # 13007 289.25 3/2/2010 TERMINATION SETTLEMENT # 13632 302.01 3/2/2010 TERMINATION SETTLEMENT # 12860 394.52 3/2/2010 #13111-VACATION SETTLEMENT 47.77 4/2/2010 TERMINATION SETTLEMENT # 13523 92.62 4/2/2010 TERMINATION SETTLEMENT # 13041 53.42 4/2/2010 TERMINATION SETTLEMENT # 13386 47.20 4/2/2010 TERMINATION SETTLEMENT # 13384 2.28 4/2/2010 TERMINATION SETTLEMENT # 13171 313.97 4/2/2010 TERMINATION SETTLEMENT # 13330 238.34 4/2/2010 TERMINATION SETTLEMENT # 13572 242.09 4/2/2010 TERMINATION SETTLEMENT # 12061 658.17 4/2/2010 TERMINATION SETTLEMENT # 13485 238.11 4/2/2010 TERMINATION SETTLEMENT # 13564 267.26 4/2/2010 TERMINATION SETTLEMENT # 13571 381.31 4/2/2010 TERMINATION SETTLEMENT # 13631 251.57 4/2/2010 TERMINATION SETTLEMENT # 13708 1,095.39 4/2/2010 TERMINATION SETTLEMENT # 13028 189.91 4/2/2010 # 11945 VACATION SETTLEMENT 183.56 4/2/2010 #11955 VACATION SETTLEMENT 64.29 6/2/2010 #10924-VACATION SETTLEMENT 150.24 6/2/2010 #11964-VACATION SETTLEMENT 120.42 6/2/2010 #13096-VACATION SETTLEMENT 87.10 6/2/2010 #12906-VACATION SETTLEMENT 60.76 6/2/2010 # 13514-TEMINATION SETTLEMENT 458.73 6/2/2010 # 13560-TEMINATION SETTLEMENT 242.08 6/2/2010 # 12099-TEMINATION SETTLEMENT 589.71 6/2/2010 # 13653-RESIGNATION SETTLEMENT 118.06 6/2/2010 # 13650-RESIGNATION SETTLEMENT 22.99 7/2/2010 TERMINATION SETTLEMENT # 13575 266.24 7/2/2010 TERMINATION SETTLEMENT # 12137 576.55 7/2/2010 TERMINATION SETTLEMENT # 13562 233.13 7/2/2010 TERMINATION SETTLEMENT # 13487 302.52 7/2/2010 TERMINATION SETTLEMENT # 13561 357.12 7/2/2010 TERMINATION SETTLEMENT # 10887 1,000.42 7/2/2010 TERMINATION SETTLEMENT # 13197 121.42 8/2/2010 TERMINATION SETTLEMENT # 13668 180.12 8/2/2010 TERMINATION SETTLEMENT # 13191 205.15 8/2/2010 TERMINATION SETTLEMENT # 13582 172.63 8/2/2010 TERMINATION SETTLEMENT # 13430 390.91 8/2/2010 TERMINATION SETTLEMENT # 13771 151.78 8/2/2010 TERMINATION SETTLEMENT # 13587 440.62 8/2/2010 TERMINATION SETTLEMENT # 13553 318.32 8/2/2010 TERMINATION SETTLEMENT # 13585 343.66 8/2/2010 TERMINATION SETTLEMENT # 13547 286.42 8/2/2010 TERMINATION SETTLEMENT # 13740 158.75 8/2/2010 TERMINATION SETTLEMENT # 13543 260.88 8/2/2010 TERMINATION SETTLEMENT # 13549 393.64 8/2/2010 TERMINATION SETTLEMENT # 13557 191.56 8/2/2010 TERMINATION SETTLEMENT # 13759 370.19 8/2/2010 TERMINATION SETTLEMENT # 13707 182.15 9/2/2010 TERMINATION SETTLEMENT # 13789 121.30 9/2/2010 TERMINATION SETTLEMENT # 13814 128.85 9/2/2010 TERMINATION SETTLEMENT # 13316 299.06 9/2/2010 TERMINATION SETTLEMENT # 13589 232.08 9/2/2010 TERMINATION SETTLEMENT # 13649 223.11 9/2/2010 TERMINATION SETTLEMENT # 13766 137.71 9/2/2010 TERMINATION SETTLEMENT # 13719 183.10 9/2/2010 TERMINATION SETTLEMENT # 12166 631.70 9/2/2010 #13184-VACATION SETTLEMENT 73.48 10/2/2010 VACATION SETTLMENT-#13042 93.22 10/2/2010 VACATION SETTLMENT-#13036 154.89 10/2/2010 VACATION SETTLMENT-#13512 84.65 10/2/2010 VACATION SETTLMENT-#13030 6.58 10/2/2010 TERMINATION SETTLEMENT # 10908 841.86 10/2/2010 TERMINATION SETTLEMENT # 13651 212.29 10/2/2010 TERMINATION SETTLEMENT # 13596 170.17 10/2/2010 TERMINATION SETTLEMENT # 13565 185.27 10/2/2010 TERMINATION SETTLEMENT # 13534 481.48 10/2/2010 TERMINATION SETTLEMENT # 12034 632.15 10/2/2010 TERMINATION SETTLEMENT # 12943 323.05 10/2/2010 TERMINATION SETTLEMENT # 13827 112.84 10/2/2010 TERMINATION SETTLEMENT # 11672 2,143.36 11/2/2010 TEMINATTION SETTLEMNET -#13816 125.24 11/2/2010 TEMINATTION SETTLEMNET -#12316 1,674.92 11/2/2010 TEMINATTION SETTLEMNET -#13141 49.65 11/2/2010 VACATION SETTLEMENT=#13642 123.27 11/2/2010 VACATION SETTLEMENT=# 13824 76.83 11/2/2010 VACATION SETTLEMENT=# 12258 55.49 14/02/2010 TERMINATION SETTLEMENT#13026 261.99 15/02/2010 #13275-VACATION SETTLEMENT 3.33 16/02/2010 #13714-VACATION SETTLEMENT 248.13 17/02/2010 #10855# RESIGNATION SETTLEMENT 829.68 18/02/2010 TERMINATION SETTLEMENT-13615 46.03 18/02/2010 TERMINATION SETTLEMENT-12382 423.92 18/02/2010 RESIGNATION SETTLEMENT-13016 183.20 18/02/2010 VACATION SETTLEMENT-13639 183.02 18/02/2010 # 13837-RESIGNATION SETTLEMTNT PAID 155.85 21/02/2010 # 13661-VAC.SETT 62.24 22/02/2010 #131321-VAC.SETT 136.74 How I can extract only the badge numbers in Column D with a formula? Thanks in advance. Jaleel |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to extract only badge numbers from such a column?
Try the below formula in cell D1 and copy down as required...
=LOOKUP(99^99,--("0"&MID(B1, MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B1&"0123456789")) ,ROW($1:$10000)))) -- Jacob "Jaleel" wrote: Excel 2003 user. I have 3 columns like this in A,B & C. 1/2/2010 #13837-SALARY PAID FOR JAN-10 120.00 1/2/2010 #13838-SALARY PAID FOR JAN-10 215.00 1/2/2010 TERMINATION SETTLEMENT # 11136 903.47 1/2/2010 TERMINATION SETTLEMENT # 12536 894.20 1/2/2010 TERMINATION SETTLEMENT # 13044 292.49 1/2/2010 TERMINATION SETTLEMENT # 13159 282.78 1/2/2010 TERMINATION SETTLEMENT # 12199 1,169.64 1/2/2010 TERMINATION SETTLEMENT # 11261 2,623.03 1/2/2010 TERMINATION SETTLEMENT # 13630 626.25 1/2/2010 TERMINATION SETTLEMENT # 11840 668.22 1/2/2010 TERMINATION SETTLEMENT # 10572 1,671.76 2/2/2010 TERMINATION SETTLEMENT # 13638 704.32 2/2/2010 TERMINATION SETTLEMENT # 13626 297.88 2/2/2010 TERMINATION SETTLEMENT # 13579 408.92 2/2/2010 TERMINATION SETTLEMENT # 13055 863.25 2/2/2010 TERMINATION SETTLEMENT # 13192 284.67 2/2/2010 TERMINATION SETTLEMENT # 13627 436.33 3/2/2010 TERMINATION SETTLEMENT # 13559 381.55 3/2/2010 TERMINATION SETTLEMENT # 12155 1,521.07 3/2/2010 TERMINATION SETTLEMENT # 13569 190.65 3/2/2010 TERMINATION SETTLEMENT # 13448 188.96 3/2/2010 TERMINATION SETTLEMENT # 13501 412.53 3/2/2010 TERMINATION SETTLEMENT # 13580 483.04 3/2/2010 TERMINATION SETTLEMENT # 13137 270.75 3/2/2010 TERMINATION SETTLEMENT # 13492 244.12 3/2/2010 TERMINATION SETTLEMENT # 13505 268.19 3/2/2010 TERMINATION SETTLEMENT # 13577 186.48 3/2/2010 TERMINATION SETTLEMENT # 13573 190.65 3/2/2010 TERMINATION SETTLEMENT # 13491 323.74 3/2/2010 TERMINATION SETTLEMENT # 13469 178.56 3/2/2010 TERMINATION SETTLEMENT # 13566 171.73 3/2/2010 TERMINATION SETTLEMENT # 13475 234.72 3/2/2010 TERMINATION SETTLEMENT # 13407 177.61 3/2/2010 TERMINATION SETTLEMENT # 13556 181.83 3/2/2010 TERMINATION SETTLEMENT # 13599 355.87 3/2/2010 TERMINATION SETTLEMENT # 10929 896.49 3/2/2010 TERMINATION SETTLEMENT # 13045 297.35 3/2/2010 TERMINATION SETTLEMENT # 12573 968.99 3/2/2010 TERMINATION SETTLEMENT # 13007 289.25 3/2/2010 TERMINATION SETTLEMENT # 13632 302.01 3/2/2010 TERMINATION SETTLEMENT # 12860 394.52 3/2/2010 #13111-VACATION SETTLEMENT 47.77 4/2/2010 TERMINATION SETTLEMENT # 13523 92.62 4/2/2010 TERMINATION SETTLEMENT # 13041 53.42 4/2/2010 TERMINATION SETTLEMENT # 13386 47.20 4/2/2010 TERMINATION SETTLEMENT # 13384 2.28 4/2/2010 TERMINATION SETTLEMENT # 13171 313.97 4/2/2010 TERMINATION SETTLEMENT # 13330 238.34 4/2/2010 TERMINATION SETTLEMENT # 13572 242.09 4/2/2010 TERMINATION SETTLEMENT # 12061 658.17 4/2/2010 TERMINATION SETTLEMENT # 13485 238.11 4/2/2010 TERMINATION SETTLEMENT # 13564 267.26 4/2/2010 TERMINATION SETTLEMENT # 13571 381.31 4/2/2010 TERMINATION SETTLEMENT # 13631 251.57 4/2/2010 TERMINATION SETTLEMENT # 13708 1,095.39 4/2/2010 TERMINATION SETTLEMENT # 13028 189.91 4/2/2010 # 11945 VACATION SETTLEMENT 183.56 4/2/2010 #11955 VACATION SETTLEMENT 64.29 6/2/2010 #10924-VACATION SETTLEMENT 150.24 6/2/2010 #11964-VACATION SETTLEMENT 120.42 6/2/2010 #13096-VACATION SETTLEMENT 87.10 6/2/2010 #12906-VACATION SETTLEMENT 60.76 6/2/2010 # 13514-TEMINATION SETTLEMENT 458.73 6/2/2010 # 13560-TEMINATION SETTLEMENT 242.08 6/2/2010 # 12099-TEMINATION SETTLEMENT 589.71 6/2/2010 # 13653-RESIGNATION SETTLEMENT 118.06 6/2/2010 # 13650-RESIGNATION SETTLEMENT 22.99 7/2/2010 TERMINATION SETTLEMENT # 13575 266.24 7/2/2010 TERMINATION SETTLEMENT # 12137 576.55 7/2/2010 TERMINATION SETTLEMENT # 13562 233.13 7/2/2010 TERMINATION SETTLEMENT # 13487 302.52 7/2/2010 TERMINATION SETTLEMENT # 13561 357.12 7/2/2010 TERMINATION SETTLEMENT # 10887 1,000.42 7/2/2010 TERMINATION SETTLEMENT # 13197 121.42 8/2/2010 TERMINATION SETTLEMENT # 13668 180.12 8/2/2010 TERMINATION SETTLEMENT # 13191 205.15 8/2/2010 TERMINATION SETTLEMENT # 13582 172.63 8/2/2010 TERMINATION SETTLEMENT # 13430 390.91 8/2/2010 TERMINATION SETTLEMENT # 13771 151.78 8/2/2010 TERMINATION SETTLEMENT # 13587 440.62 8/2/2010 TERMINATION SETTLEMENT # 13553 318.32 8/2/2010 TERMINATION SETTLEMENT # 13585 343.66 8/2/2010 TERMINATION SETTLEMENT # 13547 286.42 8/2/2010 TERMINATION SETTLEMENT # 13740 158.75 8/2/2010 TERMINATION SETTLEMENT # 13543 260.88 8/2/2010 TERMINATION SETTLEMENT # 13549 393.64 8/2/2010 TERMINATION SETTLEMENT # 13557 191.56 8/2/2010 TERMINATION SETTLEMENT # 13759 370.19 8/2/2010 TERMINATION SETTLEMENT # 13707 182.15 9/2/2010 TERMINATION SETTLEMENT # 13789 121.30 9/2/2010 TERMINATION SETTLEMENT # 13814 128.85 9/2/2010 TERMINATION SETTLEMENT # 13316 299.06 9/2/2010 TERMINATION SETTLEMENT # 13589 232.08 9/2/2010 TERMINATION SETTLEMENT # 13649 223.11 9/2/2010 TERMINATION SETTLEMENT # 13766 137.71 9/2/2010 TERMINATION SETTLEMENT # 13719 183.10 9/2/2010 TERMINATION SETTLEMENT # 12166 631.70 9/2/2010 #13184-VACATION SETTLEMENT 73.48 10/2/2010 VACATION SETTLMENT-#13042 93.22 10/2/2010 VACATION SETTLMENT-#13036 154.89 10/2/2010 VACATION SETTLMENT-#13512 84.65 10/2/2010 VACATION SETTLMENT-#13030 6.58 10/2/2010 TERMINATION SETTLEMENT # 10908 841.86 10/2/2010 TERMINATION SETTLEMENT # 13651 212.29 10/2/2010 TERMINATION SETTLEMENT # 13596 170.17 10/2/2010 TERMINATION SETTLEMENT # 13565 185.27 10/2/2010 TERMINATION SETTLEMENT # 13534 481.48 10/2/2010 TERMINATION SETTLEMENT # 12034 632.15 10/2/2010 TERMINATION SETTLEMENT # 12943 323.05 10/2/2010 TERMINATION SETTLEMENT # 13827 112.84 10/2/2010 TERMINATION SETTLEMENT # 11672 2,143.36 11/2/2010 TEMINATTION SETTLEMNET -#13816 125.24 11/2/2010 TEMINATTION SETTLEMNET -#12316 1,674.92 11/2/2010 TEMINATTION SETTLEMNET -#13141 49.65 11/2/2010 VACATION SETTLEMENT=#13642 123.27 11/2/2010 VACATION SETTLEMENT=# 13824 76.83 11/2/2010 VACATION SETTLEMENT=# 12258 55.49 14/02/2010 TERMINATION SETTLEMENT#13026 261.99 15/02/2010 #13275-VACATION SETTLEMENT 3.33 16/02/2010 #13714-VACATION SETTLEMENT 248.13 17/02/2010 #10855# RESIGNATION SETTLEMENT 829.68 18/02/2010 TERMINATION SETTLEMENT-13615 46.03 18/02/2010 TERMINATION SETTLEMENT-12382 423.92 18/02/2010 RESIGNATION SETTLEMENT-13016 183.20 18/02/2010 VACATION SETTLEMENT-13639 183.02 18/02/2010 # 13837-RESIGNATION SETTLEMTNT PAID 155.85 21/02/2010 # 13661-VAC.SETT 62.24 22/02/2010 #131321-VAC.SETT 136.74 How I can extract only the badge numbers in Column D with a formula? Thanks in advance. Jaleel |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to extract only badge numbers from such a column?
Jacob,
Wow! That's great! I have to work on lot of columns like this in many work books entered by various persons without similarity. You saved my time and effort. Thank you very much. A personal question. Which country you belong to? Jaleel "Jacob Skaria" wrote: Try the below formula in cell D1 and copy down as required... =LOOKUP(99^99,--("0"&MID(B1, MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B1&"0123456789")) ,ROW($1:$10000)))) -- Jacob "Jaleel" wrote: Excel 2003 user. I have 3 columns like this in A,B & C. 1/2/2010 #13837-SALARY PAID FOR JAN-10 120.00 1/2/2010 #13838-SALARY PAID FOR JAN-10 215.00 1/2/2010 TERMINATION SETTLEMENT # 11136 903.47 1/2/2010 TERMINATION SETTLEMENT # 12536 894.20 1/2/2010 TERMINATION SETTLEMENT # 13044 292.49 1/2/2010 TERMINATION SETTLEMENT # 13159 282.78 1/2/2010 TERMINATION SETTLEMENT # 12199 1,169.64 1/2/2010 TERMINATION SETTLEMENT # 11261 2,623.03 1/2/2010 TERMINATION SETTLEMENT # 13630 626.25 1/2/2010 TERMINATION SETTLEMENT # 11840 668.22 1/2/2010 TERMINATION SETTLEMENT # 10572 1,671.76 2/2/2010 TERMINATION SETTLEMENT # 13638 704.32 2/2/2010 TERMINATION SETTLEMENT # 13626 297.88 2/2/2010 TERMINATION SETTLEMENT # 13579 408.92 2/2/2010 TERMINATION SETTLEMENT # 13055 863.25 2/2/2010 TERMINATION SETTLEMENT # 13192 284.67 2/2/2010 TERMINATION SETTLEMENT # 13627 436.33 3/2/2010 TERMINATION SETTLEMENT # 13559 381.55 3/2/2010 TERMINATION SETTLEMENT # 12155 1,521.07 3/2/2010 TERMINATION SETTLEMENT # 13569 190.65 3/2/2010 TERMINATION SETTLEMENT # 13448 188.96 3/2/2010 TERMINATION SETTLEMENT # 13501 412.53 3/2/2010 TERMINATION SETTLEMENT # 13580 483.04 3/2/2010 TERMINATION SETTLEMENT # 13137 270.75 3/2/2010 TERMINATION SETTLEMENT # 13492 244.12 3/2/2010 TERMINATION SETTLEMENT # 13505 268.19 3/2/2010 TERMINATION SETTLEMENT # 13577 186.48 3/2/2010 TERMINATION SETTLEMENT # 13573 190.65 3/2/2010 TERMINATION SETTLEMENT # 13491 323.74 3/2/2010 TERMINATION SETTLEMENT # 13469 178.56 3/2/2010 TERMINATION SETTLEMENT # 13566 171.73 3/2/2010 TERMINATION SETTLEMENT # 13475 234.72 3/2/2010 TERMINATION SETTLEMENT # 13407 177.61 3/2/2010 TERMINATION SETTLEMENT # 13556 181.83 3/2/2010 TERMINATION SETTLEMENT # 13599 355.87 3/2/2010 TERMINATION SETTLEMENT # 10929 896.49 3/2/2010 TERMINATION SETTLEMENT # 13045 297.35 3/2/2010 TERMINATION SETTLEMENT # 12573 968.99 3/2/2010 TERMINATION SETTLEMENT # 13007 289.25 3/2/2010 TERMINATION SETTLEMENT # 13632 302.01 3/2/2010 TERMINATION SETTLEMENT # 12860 394.52 3/2/2010 #13111-VACATION SETTLEMENT 47.77 4/2/2010 TERMINATION SETTLEMENT # 13523 92.62 4/2/2010 TERMINATION SETTLEMENT # 13041 53.42 4/2/2010 TERMINATION SETTLEMENT # 13386 47.20 4/2/2010 TERMINATION SETTLEMENT # 13384 2.28 4/2/2010 TERMINATION SETTLEMENT # 13171 313.97 4/2/2010 TERMINATION SETTLEMENT # 13330 238.34 4/2/2010 TERMINATION SETTLEMENT # 13572 242.09 4/2/2010 TERMINATION SETTLEMENT # 12061 658.17 4/2/2010 TERMINATION SETTLEMENT # 13485 238.11 4/2/2010 TERMINATION SETTLEMENT # 13564 267.26 4/2/2010 TERMINATION SETTLEMENT # 13571 381.31 4/2/2010 TERMINATION SETTLEMENT # 13631 251.57 4/2/2010 TERMINATION SETTLEMENT # 13708 1,095.39 4/2/2010 TERMINATION SETTLEMENT # 13028 189.91 4/2/2010 # 11945 VACATION SETTLEMENT 183.56 4/2/2010 #11955 VACATION SETTLEMENT 64.29 6/2/2010 #10924-VACATION SETTLEMENT 150.24 6/2/2010 #11964-VACATION SETTLEMENT 120.42 6/2/2010 #13096-VACATION SETTLEMENT 87.10 6/2/2010 #12906-VACATION SETTLEMENT 60.76 6/2/2010 # 13514-TEMINATION SETTLEMENT 458.73 6/2/2010 # 13560-TEMINATION SETTLEMENT 242.08 6/2/2010 # 12099-TEMINATION SETTLEMENT 589.71 6/2/2010 # 13653-RESIGNATION SETTLEMENT 118.06 6/2/2010 # 13650-RESIGNATION SETTLEMENT 22.99 7/2/2010 TERMINATION SETTLEMENT # 13575 266.24 7/2/2010 TERMINATION SETTLEMENT # 12137 576.55 7/2/2010 TERMINATION SETTLEMENT # 13562 233.13 7/2/2010 TERMINATION SETTLEMENT # 13487 302.52 7/2/2010 TERMINATION SETTLEMENT # 13561 357.12 7/2/2010 TERMINATION SETTLEMENT # 10887 1,000.42 7/2/2010 TERMINATION SETTLEMENT # 13197 121.42 8/2/2010 TERMINATION SETTLEMENT # 13668 180.12 8/2/2010 TERMINATION SETTLEMENT # 13191 205.15 8/2/2010 TERMINATION SETTLEMENT # 13582 172.63 8/2/2010 TERMINATION SETTLEMENT # 13430 390.91 8/2/2010 TERMINATION SETTLEMENT # 13771 151.78 8/2/2010 TERMINATION SETTLEMENT # 13587 440.62 8/2/2010 TERMINATION SETTLEMENT # 13553 318.32 8/2/2010 TERMINATION SETTLEMENT # 13585 343.66 8/2/2010 TERMINATION SETTLEMENT # 13547 286.42 8/2/2010 TERMINATION SETTLEMENT # 13740 158.75 8/2/2010 TERMINATION SETTLEMENT # 13543 260.88 8/2/2010 TERMINATION SETTLEMENT # 13549 393.64 8/2/2010 TERMINATION SETTLEMENT # 13557 191.56 8/2/2010 TERMINATION SETTLEMENT # 13759 370.19 8/2/2010 TERMINATION SETTLEMENT # 13707 182.15 9/2/2010 TERMINATION SETTLEMENT # 13789 121.30 9/2/2010 TERMINATION SETTLEMENT # 13814 128.85 9/2/2010 TERMINATION SETTLEMENT # 13316 299.06 9/2/2010 TERMINATION SETTLEMENT # 13589 232.08 9/2/2010 TERMINATION SETTLEMENT # 13649 223.11 9/2/2010 TERMINATION SETTLEMENT # 13766 137.71 9/2/2010 TERMINATION SETTLEMENT # 13719 183.10 9/2/2010 TERMINATION SETTLEMENT # 12166 631.70 9/2/2010 #13184-VACATION SETTLEMENT 73.48 10/2/2010 VACATION SETTLMENT-#13042 93.22 10/2/2010 VACATION SETTLMENT-#13036 154.89 10/2/2010 VACATION SETTLMENT-#13512 84.65 10/2/2010 VACATION SETTLMENT-#13030 6.58 10/2/2010 TERMINATION SETTLEMENT # 10908 841.86 10/2/2010 TERMINATION SETTLEMENT # 13651 212.29 10/2/2010 TERMINATION SETTLEMENT # 13596 170.17 10/2/2010 TERMINATION SETTLEMENT # 13565 185.27 10/2/2010 TERMINATION SETTLEMENT # 13534 481.48 10/2/2010 TERMINATION SETTLEMENT # 12034 632.15 10/2/2010 TERMINATION SETTLEMENT # 12943 323.05 10/2/2010 TERMINATION SETTLEMENT # 13827 112.84 10/2/2010 TERMINATION SETTLEMENT # 11672 2,143.36 11/2/2010 TEMINATTION SETTLEMNET -#13816 125.24 11/2/2010 TEMINATTION SETTLEMNET -#12316 1,674.92 11/2/2010 TEMINATTION SETTLEMNET -#13141 49.65 11/2/2010 VACATION SETTLEMENT=#13642 123.27 11/2/2010 VACATION SETTLEMENT=# 13824 76.83 11/2/2010 VACATION SETTLEMENT=# 12258 55.49 14/02/2010 TERMINATION SETTLEMENT#13026 261.99 15/02/2010 #13275-VACATION SETTLEMENT 3.33 16/02/2010 #13714-VACATION SETTLEMENT 248.13 17/02/2010 #10855# RESIGNATION SETTLEMENT 829.68 18/02/2010 TERMINATION SETTLEMENT-13615 46.03 18/02/2010 TERMINATION SETTLEMENT-12382 423.92 18/02/2010 RESIGNATION SETTLEMENT-13016 183.20 18/02/2010 VACATION SETTLEMENT-13639 183.02 18/02/2010 # 13837-RESIGNATION SETTLEMTNT PAID 155.85 21/02/2010 # 13661-VAC.SETT 62.24 22/02/2010 #131321-VAC.SETT 136.74 How I can extract only the badge numbers in Column D with a formula? Thanks in advance. Jaleel |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to extract only badge numbers from such a column?
Hi Jaleel. Thanks for your feedback. I am from India.
-- Jacob "Jaleel" wrote: Jacob, Wow! That's great! I have to work on lot of columns like this in many work books entered by various persons without similarity. You saved my time and effort. Thank you very much. A personal question. Which country you belong to? Jaleel "Jacob Skaria" wrote: Try the below formula in cell D1 and copy down as required... =LOOKUP(99^99,--("0"&MID(B1, MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B1&"0123456789")) ,ROW($1:$10000)))) -- Jacob "Jaleel" wrote: Excel 2003 user. I have 3 columns like this in A,B & C. 1/2/2010 #13837-SALARY PAID FOR JAN-10 120.00 1/2/2010 #13838-SALARY PAID FOR JAN-10 215.00 1/2/2010 TERMINATION SETTLEMENT # 11136 903.47 1/2/2010 TERMINATION SETTLEMENT # 12536 894.20 1/2/2010 TERMINATION SETTLEMENT # 13044 292.49 1/2/2010 TERMINATION SETTLEMENT # 13159 282.78 1/2/2010 TERMINATION SETTLEMENT # 12199 1,169.64 1/2/2010 TERMINATION SETTLEMENT # 11261 2,623.03 1/2/2010 TERMINATION SETTLEMENT # 13630 626.25 1/2/2010 TERMINATION SETTLEMENT # 11840 668.22 1/2/2010 TERMINATION SETTLEMENT # 10572 1,671.76 2/2/2010 TERMINATION SETTLEMENT # 13638 704.32 2/2/2010 TERMINATION SETTLEMENT # 13626 297.88 2/2/2010 TERMINATION SETTLEMENT # 13579 408.92 2/2/2010 TERMINATION SETTLEMENT # 13055 863.25 2/2/2010 TERMINATION SETTLEMENT # 13192 284.67 2/2/2010 TERMINATION SETTLEMENT # 13627 436.33 3/2/2010 TERMINATION SETTLEMENT # 13559 381.55 3/2/2010 TERMINATION SETTLEMENT # 12155 1,521.07 3/2/2010 TERMINATION SETTLEMENT # 13569 190.65 3/2/2010 TERMINATION SETTLEMENT # 13448 188.96 3/2/2010 TERMINATION SETTLEMENT # 13501 412.53 3/2/2010 TERMINATION SETTLEMENT # 13580 483.04 3/2/2010 TERMINATION SETTLEMENT # 13137 270.75 3/2/2010 TERMINATION SETTLEMENT # 13492 244.12 3/2/2010 TERMINATION SETTLEMENT # 13505 268.19 3/2/2010 TERMINATION SETTLEMENT # 13577 186.48 3/2/2010 TERMINATION SETTLEMENT # 13573 190.65 3/2/2010 TERMINATION SETTLEMENT # 13491 323.74 3/2/2010 TERMINATION SETTLEMENT # 13469 178.56 3/2/2010 TERMINATION SETTLEMENT # 13566 171.73 3/2/2010 TERMINATION SETTLEMENT # 13475 234.72 3/2/2010 TERMINATION SETTLEMENT # 13407 177.61 3/2/2010 TERMINATION SETTLEMENT # 13556 181.83 3/2/2010 TERMINATION SETTLEMENT # 13599 355.87 3/2/2010 TERMINATION SETTLEMENT # 10929 896.49 3/2/2010 TERMINATION SETTLEMENT # 13045 297.35 3/2/2010 TERMINATION SETTLEMENT # 12573 968.99 3/2/2010 TERMINATION SETTLEMENT # 13007 289.25 3/2/2010 TERMINATION SETTLEMENT # 13632 302.01 3/2/2010 TERMINATION SETTLEMENT # 12860 394.52 3/2/2010 #13111-VACATION SETTLEMENT 47.77 4/2/2010 TERMINATION SETTLEMENT # 13523 92.62 4/2/2010 TERMINATION SETTLEMENT # 13041 53.42 4/2/2010 TERMINATION SETTLEMENT # 13386 47.20 4/2/2010 TERMINATION SETTLEMENT # 13384 2.28 4/2/2010 TERMINATION SETTLEMENT # 13171 313.97 4/2/2010 TERMINATION SETTLEMENT # 13330 238.34 4/2/2010 TERMINATION SETTLEMENT # 13572 242.09 4/2/2010 TERMINATION SETTLEMENT # 12061 658.17 4/2/2010 TERMINATION SETTLEMENT # 13485 238.11 4/2/2010 TERMINATION SETTLEMENT # 13564 267.26 4/2/2010 TERMINATION SETTLEMENT # 13571 381.31 4/2/2010 TERMINATION SETTLEMENT # 13631 251.57 4/2/2010 TERMINATION SETTLEMENT # 13708 1,095.39 4/2/2010 TERMINATION SETTLEMENT # 13028 189.91 4/2/2010 # 11945 VACATION SETTLEMENT 183.56 4/2/2010 #11955 VACATION SETTLEMENT 64.29 6/2/2010 #10924-VACATION SETTLEMENT 150.24 6/2/2010 #11964-VACATION SETTLEMENT 120.42 6/2/2010 #13096-VACATION SETTLEMENT 87.10 6/2/2010 #12906-VACATION SETTLEMENT 60.76 6/2/2010 # 13514-TEMINATION SETTLEMENT 458.73 6/2/2010 # 13560-TEMINATION SETTLEMENT 242.08 6/2/2010 # 12099-TEMINATION SETTLEMENT 589.71 6/2/2010 # 13653-RESIGNATION SETTLEMENT 118.06 6/2/2010 # 13650-RESIGNATION SETTLEMENT 22.99 7/2/2010 TERMINATION SETTLEMENT # 13575 266.24 7/2/2010 TERMINATION SETTLEMENT # 12137 576.55 7/2/2010 TERMINATION SETTLEMENT # 13562 233.13 7/2/2010 TERMINATION SETTLEMENT # 13487 302.52 7/2/2010 TERMINATION SETTLEMENT # 13561 357.12 7/2/2010 TERMINATION SETTLEMENT # 10887 1,000.42 7/2/2010 TERMINATION SETTLEMENT # 13197 121.42 8/2/2010 TERMINATION SETTLEMENT # 13668 180.12 8/2/2010 TERMINATION SETTLEMENT # 13191 205.15 8/2/2010 TERMINATION SETTLEMENT # 13582 172.63 8/2/2010 TERMINATION SETTLEMENT # 13430 390.91 8/2/2010 TERMINATION SETTLEMENT # 13771 151.78 8/2/2010 TERMINATION SETTLEMENT # 13587 440.62 8/2/2010 TERMINATION SETTLEMENT # 13553 318.32 8/2/2010 TERMINATION SETTLEMENT # 13585 343.66 8/2/2010 TERMINATION SETTLEMENT # 13547 286.42 8/2/2010 TERMINATION SETTLEMENT # 13740 158.75 8/2/2010 TERMINATION SETTLEMENT # 13543 260.88 8/2/2010 TERMINATION SETTLEMENT # 13549 393.64 8/2/2010 TERMINATION SETTLEMENT # 13557 191.56 8/2/2010 TERMINATION SETTLEMENT # 13759 370.19 8/2/2010 TERMINATION SETTLEMENT # 13707 182.15 9/2/2010 TERMINATION SETTLEMENT # 13789 121.30 9/2/2010 TERMINATION SETTLEMENT # 13814 128.85 9/2/2010 TERMINATION SETTLEMENT # 13316 299.06 9/2/2010 TERMINATION SETTLEMENT # 13589 232.08 9/2/2010 TERMINATION SETTLEMENT # 13649 223.11 9/2/2010 TERMINATION SETTLEMENT # 13766 137.71 9/2/2010 TERMINATION SETTLEMENT # 13719 183.10 9/2/2010 TERMINATION SETTLEMENT # 12166 631.70 9/2/2010 #13184-VACATION SETTLEMENT 73.48 10/2/2010 VACATION SETTLMENT-#13042 93.22 10/2/2010 VACATION SETTLMENT-#13036 154.89 10/2/2010 VACATION SETTLMENT-#13512 84.65 10/2/2010 VACATION SETTLMENT-#13030 6.58 10/2/2010 TERMINATION SETTLEMENT # 10908 841.86 10/2/2010 TERMINATION SETTLEMENT # 13651 212.29 10/2/2010 TERMINATION SETTLEMENT # 13596 170.17 10/2/2010 TERMINATION SETTLEMENT # 13565 185.27 10/2/2010 TERMINATION SETTLEMENT # 13534 481.48 10/2/2010 TERMINATION SETTLEMENT # 12034 632.15 10/2/2010 TERMINATION SETTLEMENT # 12943 323.05 10/2/2010 TERMINATION SETTLEMENT # 13827 112.84 10/2/2010 TERMINATION SETTLEMENT # 11672 2,143.36 11/2/2010 TEMINATTION SETTLEMNET -#13816 125.24 11/2/2010 TEMINATTION SETTLEMNET -#12316 1,674.92 11/2/2010 TEMINATTION SETTLEMNET -#13141 49.65 11/2/2010 VACATION SETTLEMENT=#13642 123.27 11/2/2010 VACATION SETTLEMENT=# 13824 76.83 11/2/2010 VACATION SETTLEMENT=# 12258 55.49 14/02/2010 TERMINATION SETTLEMENT#13026 261.99 15/02/2010 #13275-VACATION SETTLEMENT 3.33 16/02/2010 #13714-VACATION SETTLEMENT 248.13 17/02/2010 #10855# RESIGNATION SETTLEMENT 829.68 18/02/2010 TERMINATION SETTLEMENT-13615 46.03 18/02/2010 TERMINATION SETTLEMENT-12382 423.92 18/02/2010 RESIGNATION SETTLEMENT-13016 183.20 18/02/2010 VACATION SETTLEMENT-13639 183.02 18/02/2010 # 13837-RESIGNATION SETTLEMTNT PAID 155.85 21/02/2010 # 13661-VAC.SETT 62.24 22/02/2010 #131321-VAC.SETT 136.74 How I can extract only the badge numbers in Column D with a formula? Thanks in advance. Jaleel |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to extract only badge numbers from such a column?
Hi,
Your name sounded like a Keralite. That's why I asked. I am proud of being an Indian. Cheers, Jaleel "Jacob Skaria" wrote: Hi Jaleel. Thanks for your feedback. I am from India. -- Jacob "Jaleel" wrote: Jacob, Wow! That's great! I have to work on lot of columns like this in many work books entered by various persons without similarity. You saved my time and effort. Thank you very much. A personal question. Which country you belong to? Jaleel "Jacob Skaria" wrote: Try the below formula in cell D1 and copy down as required... =LOOKUP(99^99,--("0"&MID(B1, MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B1&"0123456789")) ,ROW($1:$10000)))) -- Jacob "Jaleel" wrote: Excel 2003 user. I have 3 columns like this in A,B & C. 1/2/2010 #13837-SALARY PAID FOR JAN-10 120.00 1/2/2010 #13838-SALARY PAID FOR JAN-10 215.00 1/2/2010 TERMINATION SETTLEMENT # 11136 903.47 1/2/2010 TERMINATION SETTLEMENT # 12536 894.20 1/2/2010 TERMINATION SETTLEMENT # 13044 292.49 1/2/2010 TERMINATION SETTLEMENT # 13159 282.78 1/2/2010 TERMINATION SETTLEMENT # 12199 1,169.64 1/2/2010 TERMINATION SETTLEMENT # 11261 2,623.03 1/2/2010 TERMINATION SETTLEMENT # 13630 626.25 1/2/2010 TERMINATION SETTLEMENT # 11840 668.22 1/2/2010 TERMINATION SETTLEMENT # 10572 1,671.76 2/2/2010 TERMINATION SETTLEMENT # 13638 704.32 2/2/2010 TERMINATION SETTLEMENT # 13626 297.88 2/2/2010 TERMINATION SETTLEMENT # 13579 408.92 2/2/2010 TERMINATION SETTLEMENT # 13055 863.25 2/2/2010 TERMINATION SETTLEMENT # 13192 284.67 2/2/2010 TERMINATION SETTLEMENT # 13627 436.33 3/2/2010 TERMINATION SETTLEMENT # 13559 381.55 3/2/2010 TERMINATION SETTLEMENT # 12155 1,521.07 3/2/2010 TERMINATION SETTLEMENT # 13569 190.65 3/2/2010 TERMINATION SETTLEMENT # 13448 188.96 3/2/2010 TERMINATION SETTLEMENT # 13501 412.53 3/2/2010 TERMINATION SETTLEMENT # 13580 483.04 3/2/2010 TERMINATION SETTLEMENT # 13137 270.75 3/2/2010 TERMINATION SETTLEMENT # 13492 244.12 3/2/2010 TERMINATION SETTLEMENT # 13505 268.19 3/2/2010 TERMINATION SETTLEMENT # 13577 186.48 3/2/2010 TERMINATION SETTLEMENT # 13573 190.65 3/2/2010 TERMINATION SETTLEMENT # 13491 323.74 3/2/2010 TERMINATION SETTLEMENT # 13469 178.56 3/2/2010 TERMINATION SETTLEMENT # 13566 171.73 3/2/2010 TERMINATION SETTLEMENT # 13475 234.72 3/2/2010 TERMINATION SETTLEMENT # 13407 177.61 3/2/2010 TERMINATION SETTLEMENT # 13556 181.83 3/2/2010 TERMINATION SETTLEMENT # 13599 355.87 3/2/2010 TERMINATION SETTLEMENT # 10929 896.49 3/2/2010 TERMINATION SETTLEMENT # 13045 297.35 3/2/2010 TERMINATION SETTLEMENT # 12573 968.99 3/2/2010 TERMINATION SETTLEMENT # 13007 289.25 3/2/2010 TERMINATION SETTLEMENT # 13632 302.01 3/2/2010 TERMINATION SETTLEMENT # 12860 394.52 3/2/2010 #13111-VACATION SETTLEMENT 47.77 4/2/2010 TERMINATION SETTLEMENT # 13523 92.62 4/2/2010 TERMINATION SETTLEMENT # 13041 53.42 4/2/2010 TERMINATION SETTLEMENT # 13386 47.20 4/2/2010 TERMINATION SETTLEMENT # 13384 2.28 4/2/2010 TERMINATION SETTLEMENT # 13171 313.97 4/2/2010 TERMINATION SETTLEMENT # 13330 238.34 4/2/2010 TERMINATION SETTLEMENT # 13572 242.09 4/2/2010 TERMINATION SETTLEMENT # 12061 658.17 4/2/2010 TERMINATION SETTLEMENT # 13485 238.11 4/2/2010 TERMINATION SETTLEMENT # 13564 267.26 4/2/2010 TERMINATION SETTLEMENT # 13571 381.31 4/2/2010 TERMINATION SETTLEMENT # 13631 251.57 4/2/2010 TERMINATION SETTLEMENT # 13708 1,095.39 4/2/2010 TERMINATION SETTLEMENT # 13028 189.91 4/2/2010 # 11945 VACATION SETTLEMENT 183.56 4/2/2010 #11955 VACATION SETTLEMENT 64.29 6/2/2010 #10924-VACATION SETTLEMENT 150.24 6/2/2010 #11964-VACATION SETTLEMENT 120.42 6/2/2010 #13096-VACATION SETTLEMENT 87.10 6/2/2010 #12906-VACATION SETTLEMENT 60.76 6/2/2010 # 13514-TEMINATION SETTLEMENT 458.73 6/2/2010 # 13560-TEMINATION SETTLEMENT 242.08 6/2/2010 # 12099-TEMINATION SETTLEMENT 589.71 6/2/2010 # 13653-RESIGNATION SETTLEMENT 118.06 6/2/2010 # 13650-RESIGNATION SETTLEMENT 22.99 7/2/2010 TERMINATION SETTLEMENT # 13575 266.24 7/2/2010 TERMINATION SETTLEMENT # 12137 576.55 7/2/2010 TERMINATION SETTLEMENT # 13562 233.13 7/2/2010 TERMINATION SETTLEMENT # 13487 302.52 7/2/2010 TERMINATION SETTLEMENT # 13561 357.12 7/2/2010 TERMINATION SETTLEMENT # 10887 1,000.42 7/2/2010 TERMINATION SETTLEMENT # 13197 121.42 8/2/2010 TERMINATION SETTLEMENT # 13668 180.12 8/2/2010 TERMINATION SETTLEMENT # 13191 205.15 8/2/2010 TERMINATION SETTLEMENT # 13582 172.63 8/2/2010 TERMINATION SETTLEMENT # 13430 390.91 8/2/2010 TERMINATION SETTLEMENT # 13771 151.78 8/2/2010 TERMINATION SETTLEMENT # 13587 440.62 8/2/2010 TERMINATION SETTLEMENT # 13553 318.32 8/2/2010 TERMINATION SETTLEMENT # 13585 343.66 8/2/2010 TERMINATION SETTLEMENT # 13547 286.42 8/2/2010 TERMINATION SETTLEMENT # 13740 158.75 8/2/2010 TERMINATION SETTLEMENT # 13543 260.88 8/2/2010 TERMINATION SETTLEMENT # 13549 393.64 8/2/2010 TERMINATION SETTLEMENT # 13557 191.56 8/2/2010 TERMINATION SETTLEMENT # 13759 370.19 8/2/2010 TERMINATION SETTLEMENT # 13707 182.15 9/2/2010 TERMINATION SETTLEMENT # 13789 121.30 9/2/2010 TERMINATION SETTLEMENT # 13814 128.85 9/2/2010 TERMINATION SETTLEMENT # 13316 299.06 9/2/2010 TERMINATION SETTLEMENT # 13589 232.08 9/2/2010 TERMINATION SETTLEMENT # 13649 223.11 9/2/2010 TERMINATION SETTLEMENT # 13766 137.71 9/2/2010 TERMINATION SETTLEMENT # 13719 183.10 9/2/2010 TERMINATION SETTLEMENT # 12166 631.70 9/2/2010 #13184-VACATION SETTLEMENT 73.48 10/2/2010 VACATION SETTLMENT-#13042 93.22 10/2/2010 VACATION SETTLMENT-#13036 154.89 10/2/2010 VACATION SETTLMENT-#13512 84.65 10/2/2010 VACATION SETTLMENT-#13030 6.58 10/2/2010 TERMINATION SETTLEMENT # 10908 841.86 10/2/2010 TERMINATION SETTLEMENT # 13651 212.29 10/2/2010 TERMINATION SETTLEMENT # 13596 170.17 10/2/2010 TERMINATION SETTLEMENT # 13565 185.27 10/2/2010 TERMINATION SETTLEMENT # 13534 481.48 10/2/2010 TERMINATION SETTLEMENT # 12034 632.15 10/2/2010 TERMINATION SETTLEMENT # 12943 323.05 10/2/2010 TERMINATION SETTLEMENT # 13827 112.84 10/2/2010 TERMINATION SETTLEMENT # 11672 2,143.36 11/2/2010 TEMINATTION SETTLEMNET -#13816 125.24 11/2/2010 TEMINATTION SETTLEMNET -#12316 1,674.92 11/2/2010 TEMINATTION SETTLEMNET -#13141 49.65 11/2/2010 VACATION SETTLEMENT=#13642 123.27 11/2/2010 VACATION SETTLEMENT=# 13824 76.83 11/2/2010 VACATION SETTLEMENT=# 12258 55.49 14/02/2010 TERMINATION SETTLEMENT#13026 261.99 15/02/2010 #13275-VACATION SETTLEMENT 3.33 16/02/2010 #13714-VACATION SETTLEMENT 248.13 17/02/2010 #10855# RESIGNATION SETTLEMENT 829.68 18/02/2010 TERMINATION SETTLEMENT-13615 46.03 18/02/2010 TERMINATION SETTLEMENT-12382 423.92 18/02/2010 RESIGNATION SETTLEMENT-13016 183.20 18/02/2010 VACATION SETTLEMENT-13639 183.02 18/02/2010 # 13837-RESIGNATION SETTLEMTNT PAID 155.85 21/02/2010 # 13661-VAC.SETT 62.24 22/02/2010 #131321-VAC.SETT 136.74 How I can extract only the badge numbers in Column D with a formula? Thanks in advance. Jaleel |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to extract only badge numbers from such a column?
Yes I am a Keralite..
-- Jacob "Jaleel" wrote: Hi, Your name sounded like a Keralite. That's why I asked. I am proud of being an Indian. Cheers, Jaleel "Jacob Skaria" wrote: Hi Jaleel. Thanks for your feedback. I am from India. -- Jacob "Jaleel" wrote: Jacob, Wow! That's great! I have to work on lot of columns like this in many work books entered by various persons without similarity. You saved my time and effort. Thank you very much. A personal question. Which country you belong to? Jaleel "Jacob Skaria" wrote: Try the below formula in cell D1 and copy down as required... =LOOKUP(99^99,--("0"&MID(B1, MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B1&"0123456789")) ,ROW($1:$10000)))) -- Jacob "Jaleel" wrote: Excel 2003 user. I have 3 columns like this in A,B & C. 1/2/2010 #13837-SALARY PAID FOR JAN-10 120.00 1/2/2010 #13838-SALARY PAID FOR JAN-10 215.00 1/2/2010 TERMINATION SETTLEMENT # 11136 903.47 1/2/2010 TERMINATION SETTLEMENT # 12536 894.20 1/2/2010 TERMINATION SETTLEMENT # 13044 292.49 1/2/2010 TERMINATION SETTLEMENT # 13159 282.78 1/2/2010 TERMINATION SETTLEMENT # 12199 1,169.64 1/2/2010 TERMINATION SETTLEMENT # 11261 2,623.03 1/2/2010 TERMINATION SETTLEMENT # 13630 626.25 1/2/2010 TERMINATION SETTLEMENT # 11840 668.22 1/2/2010 TERMINATION SETTLEMENT # 10572 1,671.76 2/2/2010 TERMINATION SETTLEMENT # 13638 704.32 2/2/2010 TERMINATION SETTLEMENT # 13626 297.88 2/2/2010 TERMINATION SETTLEMENT # 13579 408.92 2/2/2010 TERMINATION SETTLEMENT # 13055 863.25 2/2/2010 TERMINATION SETTLEMENT # 13192 284.67 2/2/2010 TERMINATION SETTLEMENT # 13627 436.33 3/2/2010 TERMINATION SETTLEMENT # 13559 381.55 3/2/2010 TERMINATION SETTLEMENT # 12155 1,521.07 3/2/2010 TERMINATION SETTLEMENT # 13569 190.65 3/2/2010 TERMINATION SETTLEMENT # 13448 188.96 3/2/2010 TERMINATION SETTLEMENT # 13501 412.53 3/2/2010 TERMINATION SETTLEMENT # 13580 483.04 3/2/2010 TERMINATION SETTLEMENT # 13137 270.75 3/2/2010 TERMINATION SETTLEMENT # 13492 244.12 3/2/2010 TERMINATION SETTLEMENT # 13505 268.19 3/2/2010 TERMINATION SETTLEMENT # 13577 186.48 3/2/2010 TERMINATION SETTLEMENT # 13573 190.65 3/2/2010 TERMINATION SETTLEMENT # 13491 323.74 3/2/2010 TERMINATION SETTLEMENT # 13469 178.56 3/2/2010 TERMINATION SETTLEMENT # 13566 171.73 3/2/2010 TERMINATION SETTLEMENT # 13475 234.72 3/2/2010 TERMINATION SETTLEMENT # 13407 177.61 3/2/2010 TERMINATION SETTLEMENT # 13556 181.83 3/2/2010 TERMINATION SETTLEMENT # 13599 355.87 3/2/2010 TERMINATION SETTLEMENT # 10929 896.49 3/2/2010 TERMINATION SETTLEMENT # 13045 297.35 3/2/2010 TERMINATION SETTLEMENT # 12573 968.99 3/2/2010 TERMINATION SETTLEMENT # 13007 289.25 3/2/2010 TERMINATION SETTLEMENT # 13632 302.01 3/2/2010 TERMINATION SETTLEMENT # 12860 394.52 3/2/2010 #13111-VACATION SETTLEMENT 47.77 4/2/2010 TERMINATION SETTLEMENT # 13523 92.62 4/2/2010 TERMINATION SETTLEMENT # 13041 53.42 4/2/2010 TERMINATION SETTLEMENT # 13386 47.20 4/2/2010 TERMINATION SETTLEMENT # 13384 2.28 4/2/2010 TERMINATION SETTLEMENT # 13171 313.97 4/2/2010 TERMINATION SETTLEMENT # 13330 238.34 4/2/2010 TERMINATION SETTLEMENT # 13572 242.09 4/2/2010 TERMINATION SETTLEMENT # 12061 658.17 4/2/2010 TERMINATION SETTLEMENT # 13485 238.11 4/2/2010 TERMINATION SETTLEMENT # 13564 267.26 4/2/2010 TERMINATION SETTLEMENT # 13571 381.31 4/2/2010 TERMINATION SETTLEMENT # 13631 251.57 4/2/2010 TERMINATION SETTLEMENT # 13708 1,095.39 4/2/2010 TERMINATION SETTLEMENT # 13028 189.91 4/2/2010 # 11945 VACATION SETTLEMENT 183.56 4/2/2010 #11955 VACATION SETTLEMENT 64.29 6/2/2010 #10924-VACATION SETTLEMENT 150.24 6/2/2010 #11964-VACATION SETTLEMENT 120.42 6/2/2010 #13096-VACATION SETTLEMENT 87.10 6/2/2010 #12906-VACATION SETTLEMENT 60.76 6/2/2010 # 13514-TEMINATION SETTLEMENT 458.73 6/2/2010 # 13560-TEMINATION SETTLEMENT 242.08 6/2/2010 # 12099-TEMINATION SETTLEMENT 589.71 6/2/2010 # 13653-RESIGNATION SETTLEMENT 118.06 6/2/2010 # 13650-RESIGNATION SETTLEMENT 22.99 7/2/2010 TERMINATION SETTLEMENT # 13575 266.24 7/2/2010 TERMINATION SETTLEMENT # 12137 576.55 7/2/2010 TERMINATION SETTLEMENT # 13562 233.13 7/2/2010 TERMINATION SETTLEMENT # 13487 302.52 7/2/2010 TERMINATION SETTLEMENT # 13561 357.12 7/2/2010 TERMINATION SETTLEMENT # 10887 1,000.42 7/2/2010 TERMINATION SETTLEMENT # 13197 121.42 8/2/2010 TERMINATION SETTLEMENT # 13668 180.12 8/2/2010 TERMINATION SETTLEMENT # 13191 205.15 8/2/2010 TERMINATION SETTLEMENT # 13582 172.63 8/2/2010 TERMINATION SETTLEMENT # 13430 390.91 8/2/2010 TERMINATION SETTLEMENT # 13771 151.78 8/2/2010 TERMINATION SETTLEMENT # 13587 440.62 8/2/2010 TERMINATION SETTLEMENT # 13553 318.32 8/2/2010 TERMINATION SETTLEMENT # 13585 343.66 8/2/2010 TERMINATION SETTLEMENT # 13547 286.42 8/2/2010 TERMINATION SETTLEMENT # 13740 158.75 8/2/2010 TERMINATION SETTLEMENT # 13543 260.88 8/2/2010 TERMINATION SETTLEMENT # 13549 393.64 8/2/2010 TERMINATION SETTLEMENT # 13557 191.56 8/2/2010 TERMINATION SETTLEMENT # 13759 370.19 8/2/2010 TERMINATION SETTLEMENT # 13707 182.15 9/2/2010 TERMINATION SETTLEMENT # 13789 121.30 9/2/2010 TERMINATION SETTLEMENT # 13814 128.85 9/2/2010 TERMINATION SETTLEMENT # 13316 299.06 9/2/2010 TERMINATION SETTLEMENT # 13589 232.08 9/2/2010 TERMINATION SETTLEMENT # 13649 223.11 9/2/2010 TERMINATION SETTLEMENT # 13766 137.71 9/2/2010 TERMINATION SETTLEMENT # 13719 183.10 9/2/2010 TERMINATION SETTLEMENT # 12166 631.70 9/2/2010 #13184-VACATION SETTLEMENT 73.48 10/2/2010 VACATION SETTLMENT-#13042 93.22 10/2/2010 VACATION SETTLMENT-#13036 154.89 10/2/2010 VACATION SETTLMENT-#13512 84.65 10/2/2010 VACATION SETTLMENT-#13030 6.58 10/2/2010 TERMINATION SETTLEMENT # 10908 841.86 10/2/2010 TERMINATION SETTLEMENT # 13651 212.29 10/2/2010 TERMINATION SETTLEMENT # 13596 170.17 10/2/2010 TERMINATION SETTLEMENT # 13565 185.27 10/2/2010 TERMINATION SETTLEMENT # 13534 481.48 10/2/2010 TERMINATION SETTLEMENT # 12034 632.15 10/2/2010 TERMINATION SETTLEMENT # 12943 323.05 10/2/2010 TERMINATION SETTLEMENT # 13827 112.84 10/2/2010 TERMINATION SETTLEMENT # 11672 2,143.36 11/2/2010 TEMINATTION SETTLEMNET -#13816 125.24 11/2/2010 TEMINATTION SETTLEMNET -#12316 1,674.92 11/2/2010 TEMINATTION SETTLEMNET -#13141 49.65 11/2/2010 VACATION SETTLEMENT=#13642 123.27 11/2/2010 VACATION SETTLEMENT=# 13824 76.83 11/2/2010 VACATION SETTLEMENT=# 12258 55.49 14/02/2010 TERMINATION SETTLEMENT#13026 261.99 15/02/2010 #13275-VACATION SETTLEMENT 3.33 16/02/2010 #13714-VACATION SETTLEMENT 248.13 17/02/2010 #10855# RESIGNATION SETTLEMENT 829.68 18/02/2010 TERMINATION SETTLEMENT-13615 46.03 18/02/2010 TERMINATION SETTLEMENT-12382 423.92 18/02/2010 RESIGNATION SETTLEMENT-13016 183.20 18/02/2010 VACATION SETTLEMENT-13639 183.02 18/02/2010 # 13837-RESIGNATION SETTLEMTNT PAID 155.85 21/02/2010 # 13661-VAC.SETT 62.24 22/02/2010 #131321-VAC.SETT 136.74 How I can extract only the badge numbers in Column D with a formula? Thanks in advance. Jaleel |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can print A Id badge From a Excell Work Sheet | Excel Worksheet Functions | |||
| Can Excel "extract" ranges of numbers listed in the A column? | Excel Discussion (Misc queries) | |||
| how to extract numbers from imported cell with text and numbers? | Excel Discussion (Misc queries) | |||
| how to extract unique numbers once from a list of repeated numbers? | Excel Discussion (Misc queries) | |||
| How do I extract 3 numbers inside a longer number to another column ? | Excel Discussion (Misc queries) | |||
Linear Mode
