Extract Number from String Help

**kobe4ever8** · 03-31-2011, 10:12 AM

Good morning, new member here. I need your help!!

Basically, I need a macro that extracts the 3,4,or 5 digit number after "VTF" in the Reference column and puts the extracted number into the "Number" column.

Reference---------------------------------------------------------------Number

SCS VTF-932 - WELD PROCEDURES-------------------------------932
SCS VTF -1017- PALL - INSPECTION & TEST PLAN----------1017
SCS VTF- 1080 - LIST, TERMINAL JUNCTION-------------------1080
123 SCS VTF - 1157-BILL OF MATERIALSCO1004/3008-----1157
SCS 111 VTF-1181- 2 INSTRUCTIONS HX9008/2998----------1181
KR-SS-T-0451 SCS VTF-1146 -PROCEDURES 2 --------------0451

**** Note, sometimes there would be a space between the number and "-" some times there are none. Thank you and I would appreciate any help.

**Marcol** · 03-31-2011, 11:10 AM

Try this formula
Assuming your data is in Column A beginning Row 2.
In B2

Please Login or Register  to view this content.

Drag/Fill Down

Hope this helps

**kobe4ever8** · 03-31-2011, 11:25 AM

thank you Marcol for the quick reply, your formula works on the first 5 rows.

Reference---------------------------------------------------------------Number

SCS VTF-932 - WELD PROCEDURES-------------------------------932
SCS VTF -1017- PALL - INSPECTION & TEST PLAN----------1017
SCS VTF- 1080 - LIST, TERMINAL JUNCTION-------------------1080
123 SCS VTF - 1157-BILL OF MATERIALSCO1004/3008-----1157
SCS 111 VTF-1181- 2 INSTRUCTIONS HX9008/2998----------1181
KR-SS-T-0451 SCS VTF-1146 -PROCEDURES 2 --------------SS

-- reason is (i'm assuming) that your formula finds the data between the first two "-", so that last one actually yields "SS" because thats the data between the first two "-"

Thanks!

**Marcol** · 03-31-2011, 11:45 AM

I take it that
KR-SS-T-0451 SCS VTF-1146 -PROCEDURES 2
Should return 1146 and not 0451

How posting about a sample workbook showing a range of values that might throw out the formula/code?

From Forum FAQs

How do I attach a file to a post?

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'. To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'. Clicking this button will open a new window for uploading attachments. You can upload an attachment either from your computer or from another URL by using the appropriate box on this page. Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file. To upload a file from another URL, enter the full URL for the file in the second box on this page. Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window. You can then close the window to return to the new post screen.

**kobe4ever8** · 03-31-2011, 12:05 PM

Here is the sample data. Thank you for your help.

**Marcol** · 03-31-2011, 02:09 PM

As I suspected there are a few "anomolies" in the data you have.

Try this in B7

Please Login or Register  to view this content.

Drag/Fill Down
This seems to cover all the data you have provided.

If you need the result as a number and not text use this

Please Login or Register  to view this content.

The last part *1 forces the result to a number.
The Cells can then formatted
Format > Format Cells....
Number > Custom
Type:= "0000" (The number zeros to display, so 123 will display 0123)

I have left a worksheet in the work book to let you see an easy way to build a complicated formula.

Hope this helps