The Find Function..

[Ste_Moore01]

Hi,

I'm trying to use the Find function in my spreadsheet and I've come across a problem.

When trying to find a space you use

=FIND(" ",A1,1)

If you wanted to find the first thing that isn't a space what would you use?

Thanks!

 

[Oaktree]

=FIND(LEFT(SUBSTITUTE(A1," ","")),A1)

 

[Ste_Moore01]

Thanks for the reply Oaktree but that doesn't seem to work for me.

I have a cell which I'm trying to break up. It has about 8 spaces before a word and then more spaces and a price.

It is basically the way our accounts system creates invoices etc.

I'm trying to Import them into a spreadsheet, without using macros so I was going to use formulas to work out the info rather than using VBA as I find formulas easy to work with when I learn them.

 

[Oaktree]

Please provide a sample record and how you'd like the resultant to be parsed.

Are the records all spaced the same way? (so that the first word always starts at the 9th position, etc.)?

 

[fairwinds]

Hi,

Try:

=FIND(LEFT(TRIM(A1)),A1)

 

[Ste_Moore01]

It appears like

Print Proforma Invoice.txt
	A	B	C	D
26	QUANTITY DESCRIPTION REFERENCE PRICE PER * TOTAL DISCOUNT NET VALUE
27
28	1 40 x 25 Mini Trunking
29	10L X 3M = 30M FTS4W 165.60 30M 3 165.60 93.50 % 10.76
30	4 25 x 16 S/A Trunking
31	25L X 3M = 75M FSA2W 378.00 75M 3 1512.00 93.50 % 98.28
32	1 20mm Heavy Gauge Conduit Black
33	30L X 3M = 90M FHG20/3BL 223.20 90M 3 223.20 93.70 % 14.06
Print Proforma Invoice

and I'd like it to appear

Book2
	A	B	C	D	E	F	G	H	I
1	QUANTITY	DESCRIPTION	REFERENCE	PRICE	PER	*	TOTAL	DISCOUNT	NET VALUE
2
3	1	40 x 25 Mini Trunking
4		10L X 3M = 30M	FTS4W	165.6	30M	3	165.6	93.50%	10.76
5	4	25 x 16 S/A Trunking
6		25L X 3M = 75M	FSA2W	378	75M	3	1512	93.50%	98.28
7	1	20mm Heavy Gauge Conduit Black
8		30L X 3M = 90M	FHG20/3BL	223.2	90M	3	223.2	93.70%	14.06
Sheet1

 

[fairwinds]

This seems very hard as I cannot find any consistansy in how to separate.

Even a macro would be hard to write.

I would try to get better output from account system to work with.

 

[Ste_Moore01]

The files are saved in .txt format and I can set fixed width columns but there is info above the stock details that gets split up.

It's ok though I can sort it out with ease, it just takes a couple of mintues rather than done straight away.

Thanks for the help though!