Splitting strings in cells

[Skippy]

I have a column with strings in this format

TIRES, RUBBER, SMOOTH, 18 X 5 X 12-1/8
TIRE DRIVE SMOOTH, 10-1/2 X 5 X 6-1/2 TIRE DRIVE SMOOTH, 10-1/2 X 5 X 6-1/2
SOLENOID, 24 VOLT, #841248 SOLENOID, 24 VOLT, #841248

I want to copy the non-duplicate part of each string to an adjacent column. For example:

Col B = SOLENOID, 24 VOLT, #841248

Then in adjacent columns, copy each numeric part.

Col C = 24
Col D = #841248

I have about 10,000 rows and am hoping there is a vba code that will help. Any suggestions?

 

[Barrie Davidson]

I think this formula will address the non-duplicate part:

=IF(ISERROR(FIND(LEFT(A1,FIND(",",A1)),A1,FIND(",",A1))),A1,LEFT(A1,FIND(LEFT(A1,FIND(",",A1)),A1,FIND(",",A1))-1))

How do you determine the numeric parts for the first two example lines??

 

[Skippy]

Thanks for the response Barrie. But I was looking to do this with VBA. As for your question, for the 1st example (NO DUPLICATION) it would be

Col B = TIRES, RUBBER, SMOOTH, 18 X 5 X 12-1/8
Col C = 18 X 5 X 12-1/8
Col D = ""

For the 2nd example,

Col B = TIRE DRIVE SMOOTH, 10-1/2 X 5 X 6-1/2
Col C = TIRE DRIVE SMOOTH
Col D = 10-1/2 X 5 X 6-1/2

Can this be done with VBA? As I show in my example, there may or may not be duplication and the number of "numeric strings" within the cell string are variable.

Skippy

 

[Skippy]

I'm not sure of the syntax in VBA, but I think a key to a solution would be to identify the commas. To get started, let's just work with this example:

SOLENOID, 24 VOLT, #841248 SOLENOID, 24 VOLT, #841248

First step, identify the string before the first comma (ie SOLENOID). Then look at the strings within the next commas and identify a match (i.e. SOLENOID). Delete all of the strings from that match onwards (i.e. SOLENOID, 24 VOLT, #841248) and copy the first string (i.e. SOLENOID, 24 VOLT, #841248) to the next column. Finally, look at the strings within the commas and copy numeric strings to adjacent columns (i.e. 24 and #841248)

 

[Barrie Davidson]

First step, identify the string before the first comma (ie SOLENOID). Then look at the strings within the next commas and identify a match (i.e. SOLENOID). Delete all of the strings from that match onwards (i.e. SOLENOID, 24 VOLT, #841248) and copy the first string (i.e. SOLENOID, 24 VOLT, #841248) to the next column.

That's what my formula does. Now on to your second problem...

I'm still having a problem with the logic to get the numeric parts.
How will you determine what to keep for 18 X 5 X 12-1/8? There are string characters "X" in this whereas you're dropping all string characters in 24 VOLT. So you can't write code that will drop all string characters since you won't get 18 X 5 X 12-1/8. I'd like to suggest using the comma as a field separator but that would mean "TIRES, RUBBER, SMOOTH" becomes "TIRES" "RUBBER" and "SMOOTH" (3 separate cells).

It appears this is an extract from some other system. Is there a way to extract the data using a different field separator (say "|" for example)?

 

[Skippy]

Barrie,

The solution you provided is a cell formula and I was looking for VBA code. Anyway, could you explain the logic of the formula (works good)? I'm not familiar with "IsError".

As for your last question, what if we simplify things and say that if there is a numeric character anywhere in the string between two commas, that entire string is copied to the next column:

TIRE DRIVE SMOOTH, 10-1/2 X 5 X 6-1/2             10-1/2 X 5 X 6-1/2 
SOLENOID, 24 VOLT, #841248                            24 VOLT                  #841248