Splitting strings in cells

Skippy

Board Regular
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?

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Barrie Davidson

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

Code:
``=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

Board Regular
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

Board Regular
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

MrExcel MVP
Skippy said:
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

Board Regular
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:

Code:
``````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``````

Replies
1
Views
478
Replies
1
Views
84
Replies
2
Views
203
Replies
4
Views
209
Replies
1
Views
377

1,195,855
Messages
6,011,974
Members
441,660
Latest member
Neela_Kattappa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back