# The Find Function..

#### Ste_Moore01

##### Active Member
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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### Oaktree

##### MrExcel MVP
=FIND(LEFT(SUBSTITUTE(A1," ","")),A1)

#### Ste_Moore01

##### Active Member
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

##### MrExcel MVP
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

##### MrExcel MVP
Hi,

Try:

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

#### Ste_Moore01

##### Active Member
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

#### fairwinds

##### MrExcel MVP
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

##### Active Member
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!

Replies
23
Views
893
Replies
15
Views
373
Replies
5
Views
502
Replies
7
Views
116
Replies
1
Views
141

1,196,013
Messages
6,012,857
Members
441,735
Latest member
melastro

### 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.

### Which adblocker are you using?

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

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