Need to pull part of a description into different column

Hi,

I'm fairly new to VBA, and have spent all afternoon searching for a solution to my problem but could not really find what i was looking for.
(or convert anything that was close enough into whatever I needed...)

Probably there is a very simple trick to do this, so any help would be highly appreciated!


The problem:

In my document, column F is occupied with a description of a product which is supplied by our vendor.
The description starts with a part number (which is not always the same length in characters!), is then followed by a comma (,) which is then followed by the remainder of the description.

what I need my macro to do for me is to extract the part number and put this is column E.
The remainder of the description does not need to be captured in any other column.

The tricky bit is that some of the descriptions on columns F contain more than 1 comma. Please see below 3 descriptions as examples:
CAB-ETHXOVER=, ETHERNET CROSS-OVER CABLE
CISCO2911/K9, Cisco 2911 w/3 GE,4 EHWIC,2 DSP,1 SM,256MB CF,512MB DRAM,IPB
VWIC2-1MFT-G703=, 1-Port 2nd Gen Multiflex Trunk Voice/WAN Int. Card - G.703


Keeping my fingers crossed for a good solution....
Thanks! Marijke

Here's what I came up with:

Please Login or Register  to view this content.

It also takes out the space between the number and the description. You'll need to add a loop to use it on the whole column.

Woah, my bad, I thought you wanted the description without the part number. This gives you the part number, no description:

Please Login or Register  to view this content.

I included the loop for you. This assumes there is no title row and your descriptions start in row 1.

Thanks chris.c
tried it, and it works but... it works the other way around on my end;
it pulls the 2nd part of the description into column E, but I need the 1st part.


Also, how can I create the loop to use it on the whole column?
Cell Z1 contains a 'counta' formula

Range("Z1").Formula = "=counta(F1:F65000)"
x = Range("Z1").Value

Noticed that. See my second post above.

works perfectly!!!! Thanks chris.c