extract text from an entire string

[MAP]

Excel experts, I have checked other posts related to extracting text, but I am not able to understand how I can implement that to my needs.

In column A, I have 2,000 rows of the text string that resembles the following:
"07/16 07/14 1234 WM SUPERCENTER #1234 HOUSTON TX $5.99"

A) The first five characters will need to be extracted to column D as a date formatted as mm/yy
B) the second set of five characters after the space will need to be extracted to column E as a date formatted as mm/yy
C) the 4-digit numbers are not used
D) the description will be a variable length that ends before the "$" ... this needs to be extracted to column F
E) the dollar value can be of variable length after the "$" which needs to be extracted to column G and formatted as currency

I am using Excel 2003 and I am not familiar with macros. What formula can you experts offer to help me with my challenge?

Your assistance will be appreciated. Thank you/

 

[Peter_SSs]

See if these do what you want. Not sure how the date parts will go for you as you will have different regional date settings to me, but in any case this will be possible.
You will need to format the date columns as you want with "mm/yy" and also format the currency column as the formula will just extract the number.

Excel Workbook
	A	B	C	D	E	F	G
2	07/16 07/14 1234 WM SUPERCENTER #1234 HOUSTON TX $5.99			07/16	07/14	1234 WM SUPERCENTER #1234 HOUSTON TX	$5.99
Split Text

 

[MAP]

Peter_SSs, I thank you for your prompt response. I tried your formulas but they have not worked for me. I am not sure why.

for the =DATEVALUE("25/"&LEFT(A2,5)) and the =DATEVALUE("25/"&MID(A2,7,5)) I am getting a "VALUE! error
for the =TRIM(REPLACE(LEFT(A2,FIND("$",A2)-1),1,11,"")) Excel halts me with "The formula you typed contains an error"
the =REPLACE(A2,1,FIND("$",A2),"")+0 seems to be working properly

Am I doing something wrong?

 

[jtakw]

Hi,

Try these, for Column F, if the "4 digit" number is Always 4 digits, use F2 formula, if it's variable length, use F1 formula:

Book1
	A	B	C	D	E	F	G
1	07/16 07/14 1234 WM SUPERCENTER #1234 HOUSTON TX $5.99			07/16	07/14	WM SUPERCENTER #1234 HOUSTON TX	$5.99
2						WM SUPERCENTER #1234 HOUSTON TX
3				Format as	Format as		Format as
4				mm/yy	mm/yy		Currency
Sheet201

Cell Formulas
Range	Formula
F2	=TRIM(MID(A1,FIND(" ",A1,17),FIND("$",A1)-17))
F1	=TRIM(SUBSTITUTE(MID(A1,FIND(" ",A1,13),255),MID(A1,FIND("$",A1),30),""))
D1	=(LEFT(A1,3)&"1/"&MID(A1,4,2))+0
E1	=(MID(A1,7,3)&"1/"&MID(A1,10,2))+0
G1	=REPLACE(A1,1,FIND("$",A1),"")+0

 

[Peter_SSs]

I tried your formulas but they have not worked for me. I am not sure why.

As I mentioned, our regional date settings are almost certainly different. My standard dates (Australia) are in d/m/y format and no doubt yours are m/d/y format. The alternatives I would have suggested for the dates would be
=SUBSTITUTE(LEFT(A2,5),"/","/1/")+0
=SUBSTITUTE(MID(A2,7,5),"/","/1/")+0

I don't know why the column F formula is giving you that message but I realise that my formula result included the 1234 that you didn't want. My column F formula should have been
=REPLACE(LEFT(A2,FIND("$",A2)-1),1,17,"")

 

[jtakw]

Hi MAP & Peter,

I just did a quick check, according to Microsoft, Both TRIM and SUBSTITUTE functions are Not supported until Excel 2007...

Here're my updated formulas:

Book1
	A	B	C	D	E	F	G
1	07/16 07/14 1234 WM SUPERCENTER #1234 HOUSTON TX $5.99			07/16	07/14	WM SUPERCENTER #1234 HOUSTON TX	$5.99
2
3				Format as	Format as		Format as
4				mm/yy	mm/yy		Currency
Sheet201

Cell Formulas
Range	Formula
D1	=(LEFT(A1,3)&"1/"&MID(A1,4,2))+0
E1	=(MID(A1,7,3)&"1/"&MID(A1,10,2))+0
F1	=MID(A1,18,FIND("$",A1)-19)
G1	=REPLACE(A1,1,FIND("$",A1),"")+0

Assumes the 4 digit number is Always 4 digits.

 

[Peter_SSs]

Hi MAP & Peter,

I just did a quick check, according to Microsoft, Both TRIM and SUBSTITUTE functions are Not supported until Excel 2007...

I'm not sure that is correct. Can you point to anything that specifically says that, or is it just that Excel 2003 is not in the list of "Applies to .. " for those functions?
If it is just that they are not in the list, that is because Excel 2003 is not supported any more. Even the SUM() function lists 2007 as the earliest version.
In any case, If you use a function that does not exist in your version, you usually get a #NAME? error, not the error reported by the OP

 

[Rick Rothstein]

Hi MAP & Peter,

I just did a quick check, according to Microsoft, Both TRIM and SUBSTITUTE functions are Not supported until Excel 2007...

I'm not sure that is correct.

It is definitely not correct. I have XL2003 installed on an old laptop and I just checked to be sure... both functions are available in XL2003 for sure.

 

[jtakw]

You're probably right Peter, and Rick for sure (since you have 2003), yeah, it's just Not on the list of "Applies to..."

Rick, does 2003 support TRIM?

 

[Peter_SSs]

Rick, does 2003 support TRIM?

.

... both functions are available in XL2003 for sure.