Format Syntax

[JW]

Hi, I'm looking to extract agreement numbers from one spreadsheet to a new one. These agreements will either be ###-######-## or ###.######.## (where # is a number between 0 and 9), and where one exists in the original there will be one agreement per row . Can anyone help me with the correct syntax to recognise these two formats, using the FIND function. Thanks for any assistance (J)

 

[fairwinds]

Does this help you?
(change ; in formulas to ,)

Book1
	A	B	C	D
2
3	123-456789-12		123-456789-12
4	123.456789.12		123.456789.12
5	123.4565789.13
6	123.456789-14
7	123.456789.15		123.456789.15
8	123.456789.16		123.456789.16
9	123.456789.173
10	1234.456789.18
11	123.456789.19		123.456789.19
12
Sheet1

 

[Chitosunday]

replace # with ? as # is not defind in find. or the , the object should be text and not number. The ? will accept even alphabet but you have no choice. Anyway, the - and , will be the special character to find which can be enough to serve your requirement.

 

[JW]

Thanks for the responses so far. I'm getting my data via OCR and text file from printed invoices and the differences in spacing are causing me the problems. Below is a snapshot of what I'm working with (sorry, Coolio's 2.42 has hit a snag), and I'd like to:
1) Identify each agreement number (ie 267-611194-28) and
2) Identify the first (net) amount associated with this (ie £S9S.42)

I thought this would be best using VBA, but I'm still struggling to select the values and paste them into columns A and B of a new sheet (say "TotalSheet") in the same workbook. Can anyone throw some further light on the syntax? Thanks again (J)

267-611194-28 ******* £S9S.42 £104.20 £699.62
267-623978-01 ******* £74.19 £12.98 £87.17
267-6S3348-09 ****** £86.70 £IS.17 £101.87 .
267-6S3382-13 ******* £108.66 £19.02 £127.68 .
306 OL. 267-653405-18 ******* £35.97 £6.29 £42.26
106 XN 267-65344S-20 ******* £4.S8 £0.80 £S.38 .
106 XN 267-653446-20 ******* £4.S8 £0.80 £5.38 .
267-6535S5-14 ******* £11.22 £1.96 £13.18
267.6S3567-09 ******* £79.66 £13.94 £93.60

 

[fairwinds]

Hi,
£S9S.42 what value is that supposed to generate? It is S's right? not 5?

 

[JW]

That's the OCR for you, should read £595.42 - I wondered if they could all be picked up by (currency) format

 

[fairwinds]

What about this?
Column B C and D is just to avoid an extremely long formula.
This seems to work for at least the data example you provided.

Book2.xls
	A	B	C	D	E	F
1	Original				Number	Value
2	267-611194-28*******595.42104.20699.62	14	22	1	267-611194-28	595.42
3	267-623978-01*******74.1912.9887.17	14	22	1	267-623978-01	74.19
4	267-6S3348-09******86.70IS.17101.87.	14	21	1	267-6S3348-09	86.70
5	267-6S3382-13*******108.6619.02127.68.	14	22	1	267-6S3382-13	108.66
6	306OL.267-653405-18*******35.976.2942.26	4	8	8	267-653405-18	35.97
7	106XN267-65344S-20*******4.580.80S.38.	4	7	7	267-65344S-20	4.58
8	106XN267-653446-20*******4.580.805.38.	4	7	7	267-653446-20	4.58
9	267-6535S5-14*******11.221.9613.18	14	22	1	267-6535S5-14	11.22
10	267.6S3567-09*******79.6613.9493.60	14	22	1	267.6S3567-09	79.66
Sheet2

 

[fairwinds]

Seems like you cannot wiew the formulas I posted. I wonder why? It has happend before!

Anyway:
B2 =FIND(" ",A2)
C2 =FIND(" ",A2,FIND(" ",A2)+1)
D2 =MAX(IF(B2<13,B2,1),IF(C2<13,C2,1))
E2 =MID(A2,MIN(FIND("-",A2,D2),(FIND(".",A2,D2)))-3,13)
F2 =MID(A2,FIND("£",A2,1)+1,FIND(" ",A2,FIND("£",A2,1))-FIND("£",A2,1))

 

[JW]

Many thanks, Fairwinds