selecting a group of 4 numbers from within a string of 8 by matching to a data base

[markdh]

I have a spreadsheet that has over 2000 items. I need to copy and paste the numbers from one spread sheet to the other or just change the original and save it as the new one, anyway I need to remove letters and some numbers based on the following...
Each item has a number assigned to it. Item numbers varies in how many digits there are....they are not all eight characters long, but most are. They all have at least 4 numbers that are side by side that I need to reference/keep, some item numbers have letters also that need to be removed....example below. I need to change the item number so that I only have the main first 4 consecutive numbers in the item number that are not a zero unless there are two leading zeros . example 003455 is a jasmin plant that needs to be changed to 0345

item number.............. item .................................................. new item number
OR067342 ------------AGAVE DESMETTIANA VARIEGATA -----------6734
006375 ---------------CAESALPINIA GILLIESII ----------------------0637
GA09732PT ------------- FERN-ATHYRIUM REGAL RED ----------------9732
003455 -----------------JASMINUM FLORIDUM ------------------------ 0345
CA072941 ------------ YUCCA FIL GOLDEN SWORD ------------------7294


the item doesnt really matter its just to give you an idea of whats going on. I need to change one item number into another with a formula

each is in a column and individual cells and there are over 2000


anyone ?
thanks!!!!!!

[NickyC]

hi
does every item that begins with 00 have 3 numbers after, or could there be eg 00VB1234

[NickyC]

hi
try this user-defined function

Please Login or Register  to view this content.

[Marcol]

Are you asking for the integer of the contained number divided by 10?
Why in that case is 9732 not 973?

If so try these formulae
To return a number ...

Please Login or Register  to view this content.

Or, to return a fixed length string ...

Please Login or Register  to view this content.

[Marcol]

Oops! Just re-read your original post ...

Try this workbook.
The helper column can be hidden.
Use either Column E or F as suits your needs.

[markdh]

yes , always

[markdh]

must have the leading zero if there are two in the original

[markdh]

yes . always and no letters after the zeros....ever....unless attached to the very end of the item number

[markdh]

can I call you, Im new at this and dont understand how to apply this....or call me when ever you can
559-788-2377

[markdh]

Im a newby at this and dont understand completely, The spread sheet it will be applied to is a fixed template. Row 1 is taken by tiles, rows 2 through 3000 are item numbers how do I apply the formula you gave me to 3000 rows starting with row 2?
Are you in scotland? It would be nice if we could chat on the phone about it. or email directly

[markdh]

oh wow your in australia........

[NickyC]

Hi markdh
yes, it's an international forum
Marcol's formula is probably the easiest way to go
if you want to try my user-defined formula, but are not sure what to do, try this:

To copy and use a user-defined formula

• press alt+F11 to open the VBA area
• select insert > module
• copy the code from this website and paste it into the VBA module you just inserted
• Return to the spreadheet. You now have a new formula that works like the inbuilt formulas such as =SUM()
• In an empty cell, type:
  =ItemNo(A2)
  or whatever the address to return the item number from

If you cannot see the developer tab

• Click the File tab
• Click Options
• Click Customize Ribbon
• Under Customize the Ribbon and under Main Tabs, select the Developer check box