Parse String from Columns and put into two separate columns

mrmmickle1

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,461
I have data that looks like the following:

Excel 2010
ABCDEF

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Part Number & Description[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Qty[/TD]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]Cost[/TD]
[TD="align: center"]CM[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]3607127-1 TUBE ASSY[/TD]
[TD="align: center"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$243.00[/TD]
[TD="align: right"]$162.00[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]AN960C716 WASHER[/TD]
[TD="align: center"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$0.15[/TD]
[TD="align: right"]$0.10[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]AN960C616L WASHER[/TD]
[TD="align: center"][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]$0.72[/TD]
[TD="align: right"]$0.48[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]AN960C616 WASHER[/TD]
[TD="align: center"][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]$1.44[/TD]
[TD="align: right"]$0.96[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]3863237-1 SEAL[/TD]
[TD="align: center"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$308.25[/TD]
[TD="align: right"]$205.50[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]S9413-035 PACKING[/TD]
[TD="align: center"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$1.05[/TD]
[TD="align: right"]$0.70[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]AN960C10L WASHER[/TD]
[TD="align: center"][/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]$3.75[/TD]
[TD="align: right"]$2.50[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]583-508-9008 PACKING W/RET.[/TD]
[TD="align: center"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$3.38[/TD]
[TD="align: right"]$2.25[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]S9413-126 PACKING[/TD]
[TD="align: center"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$0.68[/TD]
[TD="align: right"]$0.45[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]S9413-010 PACKING[/TD]
[TD="align: center"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$0.12[/TD]
[TD="align: right"]$0.08[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]693616 SEAL AIR OIL CPRSR[/TD]
[TD="align: center"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$2,647.50[/TD]
[TD="align: right"]$1,765.00[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]379510-2 SHIM 2STG DIFF[/TD]
[TD="align: center"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$84.60[/TD]
[TD="align: right"]$56.40[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]AN960C416L WASHER[/TD]
[TD="align: center"][/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]$6.60[/TD]
[TD="align: right"]$4.40[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]S9413-012 PACKING[/TD]
[TD="align: center"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$0.18[/TD]
[TD="align: right"]$0.12[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]S9413-011 PACKING[/TD]
[TD="align: center"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$0.18[/TD]
[TD="align: right"]$0.12[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]S9009D6 GASKET[/TD]
[TD="align: center"][/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]$4.50[/TD]
[TD="align: right"]$3.00[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]AN960C416 WASHER[/TD]
[TD="align: center"][/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]$2.40[/TD]
[TD="align: right"]$1.60[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]S8990-604 PACKING[/TD]
[TD="align: center"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]$1.26[/TD]
[TD="align: right"]$0.84[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]S9413-014 PACKING[/TD]
[TD="align: center"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]$1.50[/TD]
[TD="align: right"]$1.00[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]21[/TD]
[TD="align: center"]S9413-558 PACKING, PREFORM - FLUOROCARBON[/TD]
[TD="align: center"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]$2.84[/TD]
[TD="align: right"]$1.89[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]22[/TD]
[TD="align: center"]S9413-554 PACKING[/TD]
[TD="align: center"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]$2.70[/TD]
[TD="align: right"]$1.80[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]23[/TD]
[TD="align: center"]AN960C10 WASHER[/TD]
[TD="align: center"][/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]$1.35[/TD]
[TD="align: right"]$0.90[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]24[/TD]
[TD="align: center"]S9413-028 PACKING[/TD]
[TD="align: center"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$0.51[/TD]
[TD="align: right"]$0.34[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]25[/TD]
[TD="align: center"]S9413-029 PACKING[/TD]
[TD="align: center"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$0.51[/TD]
[TD="align: right"]$0.34[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]26[/TD]
[TD="align: center"]S9413-032 PACKING[/TD]
[TD="align: center"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$0.75[/TD]
[TD="align: right"]$0.50[/TD]
[TD="align: center"]33.3%[/TD]

</tbody>
Sheet1​



I would like to separate out the data in column A to look like this:
Excel 2010
ABCDEFG

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Part Number & Description[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Qty[/TD]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]Cost[/TD]
[TD="align: center"]CM[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]3607127-1 [/TD]
[TD="align: center"][/TD]
[TD="align: center"]TUBE ASSY[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$243.00[/TD]
[TD="align: right"]$162.00[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]AN960C716 [/TD]
[TD="align: center"][/TD]
[TD="align: center"] WASHER[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$0.15[/TD]
[TD="align: right"]$0.10[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]AN960C616L [/TD]
[TD="align: center"][/TD]
[TD="align: center"] WASHER[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]$0.72[/TD]
[TD="align: right"]$0.48[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]AN960C616 [/TD]
[TD="align: center"][/TD]
[TD="align: center"] WASHER[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]$1.44[/TD]
[TD="align: right"]$0.96[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]3863237-1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]SEAL[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$308.25[/TD]
[TD="align: right"]$205.50[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]S9413-035 [/TD]
[TD="align: center"][/TD]
[TD="align: center"] PACKING[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$1.05[/TD]
[TD="align: right"]$0.70[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]AN960C10L [/TD]
[TD="align: center"][/TD]
[TD="align: center"] WASHER[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]$3.75[/TD]
[TD="align: right"]$2.50[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]583-508-9008 [/TD]
[TD="align: center"][/TD]
[TD="align: center"]PACKING W/RET.[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$3.38[/TD]
[TD="align: right"]$2.25[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]S9413-126 [/TD]
[TD="align: center"][/TD]
[TD="align: center"]PACKING[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$0.68[/TD]
[TD="align: right"]$0.45[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]S9413-010 [/TD]
[TD="align: center"][/TD]
[TD="align: center"]PACKING[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$0.12[/TD]
[TD="align: right"]$0.08[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]693616[/TD]
[TD="align: center"][/TD]
[TD="align: center"]SEAL AIR OIL CPRSR[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$2,647.50[/TD]
[TD="align: right"]$1,765.00[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]379510-2 [/TD]
[TD="align: center"][/TD]
[TD="align: center"]SHIM 2STG DIFF[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$84.60[/TD]
[TD="align: right"]$56.40[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]AN960C416L [/TD]
[TD="align: center"][/TD]
[TD="align: center"]WASHER[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]$6.60[/TD]
[TD="align: right"]$4.40[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]S9413-012 [/TD]
[TD="align: center"][/TD]
[TD="align: center"]PACKING[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$0.18[/TD]
[TD="align: right"]$0.12[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]S9413-011 [/TD]
[TD="align: center"][/TD]
[TD="align: center"]PACKING[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$0.18[/TD]
[TD="align: right"]$0.12[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]S9009D6 [/TD]
[TD="align: center"][/TD]
[TD="align: center"]GASKET[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]$4.50[/TD]
[TD="align: right"]$3.00[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]AN960C416 [/TD]
[TD="align: center"][/TD]
[TD="align: center"]WASHER[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]$2.40[/TD]
[TD="align: right"]$1.60[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]S8990-604 [/TD]
[TD="align: center"][/TD]
[TD="align: center"]PACKING[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]$1.26[/TD]
[TD="align: right"]$0.84[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]S9413-014 [/TD]
[TD="align: center"][/TD]
[TD="align: center"]PACKING[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]$1.50[/TD]
[TD="align: right"]$1.00[/TD]
[TD="align: center"]33.3%[/TD]

[TD="align: center"]21[/TD]
[TD="align: center"]S9413-558 [/TD]
[TD="align: center"][/TD]
[TD="align: center"]PACKING, PREFORM - FLUOROCARBON[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]$2.84[/TD]
[TD="align: right"]$1.89[/TD]
[TD="align: center"]33.3%[/TD]

</tbody>
Sheet1​



The first string of data would always need to be in column A. The rest of the data needs to go in column B. Note that the description will sometimes contain multiple words. But I will always only need the first string. The first string can be alpha and numeric. The rest of the data can also contain numbers but is mostly text. Any help with solving this would be much appreciated. I want to parse the numbers so I can compare them on another sheet with a Vlookup however this is the way the information comes out of the database....all in one cell :(

Please note that cells A and B are merged and are not showing up in the html maker. However I can access the data only in cell A if need be. I merged the columns on request. I don't particularly favor them. So you can act as if the cells are not merged and you are trying to separate the data out into only columns A and B. Sorry for the inaccurate picture.

Thanks Again.
 
Last edited:

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
In cell "B2", put this formula and copy down as far as required
Code: 
=MID(A2,FIND(" ",A2,1),99)
Also, try to avoid Merged cells at all costs, they will give you nightmares.
Instead, look at using Format cells / Alignment / "Center Across Selection"
 
Upvote 0
Thanks! You have helped me several times this evening. I am having a good night. Just learning is frustrating but, things are starting to come together. I am beginning to not just understand bits and pieces of code but large portions of it!

Also I noticed that this gives me the text after the number. Is there a simple way to get the first part of the string in a separate column as well?
 
Last edited:
Upvote 0
Try, and copy down as required
Code: 
=LEFT(A2,FIND(" ",A2,1)-1)
 
Upvote 0
Thanks so much. I was just messing with it and came up with this:

=LEFT(A2,FIND(" ",A2,1))

It seemed to work as well. You made me push myself a little bit. :)
 
Upvote 0
Keep in mind though, that your formula will include the space at the end of the number string, whereas mine doesn't.
The space could also cause issues when doing other calcs, that's why it's left out of the result......usually !!
 
Upvote 0
Awesome. I was wondering about that. I was thinking about using Trim. This solves the issue!
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,609
Messages
6,179,875
Members
452,949
Latest member
Dupuhini

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top