Best way break apart Character String

[tdp3290]

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

 

[Akashwani]

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

 

[Special-K99]

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)))))

 

[tdp3290]

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?

 

[tdp3290]

OOOPS..sory Special K99.. NOT KKK... big difference.. my apologies!!!

 

[Special-K99]

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

 

[tdp3290]

The first entry from the file. chevy070522N5800

 

[Special-K99]

That works fine with all formulas, check the formulas youve typed in.

By the way Im using Excel 2007

 

[tdp3290]

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

 

[Special-K99]

Works fine on all those entries
Try Downloading my spreadsheet

http://rapidshare.com/files/348763101/Book2.xlsx.html