The Find Function..

Ste_Moore01

Active Member
Joined
Jul 13, 2005
Messages
467
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!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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.
 
Upvote 0
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.)?
 
Upvote 0
It appears like
Print Proforma Invoice.txt
ABCD
26QUANTITY DESCRIPTION REFERENCE PRICE PER * TOTAL DISCOUNT NET VALUE
27
281 40 x 25 Mini Trunking
2910L X 3M = 30M FTS4W 165.60 30M 3 165.60 93.50 % 10.76
304 25 x 16 S/A Trunking
3125L X 3M = 75M FSA2W 378.00 75M 3 1512.00 93.50 % 98.28
321 20mm Heavy Gauge Conduit Black
3330L 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
ABCDEFGHI
1QUANTITYDESCRIPTIONREFERENCEPRICEPER*TOTALDISCOUNTNET VALUE
2
3140 x 25 Mini Trunking
410L X 3M = 30MFTS4W165.630M3165.693.50%10.76
5425 x 16 S/A Trunking
625L X 3M = 75MFSA2W37875M3151293.50%98.28
7120mm Heavy Gauge Conduit Black
830L X 3M = 90MFHG20/3BL223.290M3223.293.70%14.06
Sheet1
 
Upvote 0
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.
 
Upvote 0
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! :LOL:
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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