Best way break apart Character String

tdp3290

Board Regular
Joined
Feb 15, 2007
Messages
58
Hi guys,

Here is my problem. I have a .csv file that has thousands of entries of car makes, models, year etc. Unfortunately my customer has given me the file from their DB and each entry with all the data is in a SINGLE CELL for each record. I need to break apart each of these and place them into separate columns. So here is the current structure:

chevy070522N5800

Which should be broken into 4 columns with the following headings:

Make / Date of Delivery / New-Used / Inv. Price
Chevy 070522 N 5800

When parsing the first few characters, keep in mind that this first part (Chevy in this example) may have any number of Characters from 1-5, but no more than 5. I assume I should just look at all alpha characters up until the first date character and strip those all off and put them into another adjacent column? The same is true of the last part of the entry...

Inv. Price can have up to 7 Characters in their entry. Again I assumed that I should just strip all numeric digits from the end of the entry and place those in another column and then just convert that to a currency format?

Any thoughts on the best way to accomplish this would be greatly appreciated. I just bought the Mr Excel Live Lessons Set from Borders yesterday.. even if someone could point me to the right place to start doing my own research on this..again appreciated.

Thanks
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Hi,

Go to the Excel Is Fun link below and type in the search box...

Extract Text OR Extract

That's the best I can do for now, sorry.

Ak
 
Upvote 0
This will extract the date

In B1
=RIGHT("0"&LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))),6)

This will extract the car make
In C1
=LEFT(A1,FIND(B1,A1)-1)

This will extract the New/Used character
In D1
=MID(A1,LEN(B1)+LEN(C1)+1,1)

This will extract the invoice amount
In E1
=RIGHT(A1,LEN(A1)-(LEN(B1)+(LEN(C1)+(LEN(D1)))))
 
Upvote 0
Thanks Special KKK,

I put the value from the first entry into A1 and copied each of your suggestions into B1, C1, D1 and E1.

In B1 it returns "True" and all other columns say #Value!

??????

Thanks for your help again.... do you know what might be wrong here?
 
Upvote 0
What is the value in A1 when B1 is TRUE?

The formulas worked fine on the example you gave
Can you post a small selection of the data here, maybe a dozen or so just in case formulas dont work on all of your data

KKK, K99 doesnt matter :)
 
Upvote 0
That works fine with all formulas, check the formulas youve typed in.

By the way Im using Excel 2007
 
Upvote 0
Chevy070522N5800
Audi020211U8900
Kia020614U0875.0
Toyot991011U225000
Toyot011126U01750
Chevy06020918U06500
Ford020914U2200

This is some random entries...

I copied and pasted your suggestions... so there should be no typos??
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

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