Simplier 'FIND' between comma's formula?

Hey all,

I've got a string of text in column A, such as below from which I want the numbers 58791 between commas 2 and 3.

Please Login or Register  to view this content.

I have been able to do so using the formula below, however was wondering if there was a more condensed way of going about things?

Please Login or Register  to view this content.

Sorry yes, the number returned will always be between commas 2 and 3.

I should have mentioned that while there is the option to use text to columns, I'm unable to as I need to return the whole string line in another worksheet using an Index Match Lookup using the middle number.

Sheet 1

A	B
Line1,ABCDORG,1234567,Test Data, Test Addrress	1234567
Line1,ABCDORG,15485,Test Data, Test Addrress	15485
Line1,ABCDORG,68787,Test Data, Test Addrress	68787

Sheet 2

A	B
15485	Line1,ABCDORG,15485,Test Data, Test Addrress

Where B contains an Index/Match for lookup in 'Sheet 1' Column B returning Sheet 1 Column A.

Originally Posted by DHHM

I should have mentioned that while there is the option to use text to columns, I'm unable to as I need to return the whole string line in another worksheet using an Index Match Lookup using the middle number.

Why does that mean you can't use Text To Columns?

Text to columns doesn't have to be 'destructive' of the original data.
You can specify to put the results in another area of your sheet, leaving the original data in tact.

On the 3rd screen in the wizard, there's a box called 'Destination'
That can be anywhere you choose on the sheet.

Originally Posted by Jonmo1

Why does that mean you can't use Text To Columns?

Text to columns doesn't have to be 'destructive' of the original data.
You can specify to put the results in another area of your sheet, leaving the original data in tact.

On the 3rd screen in the wizard, there's a box called 'Destination'
That can be anywhere you choose on the sheet.

I had no idea you could do that.

Originally Posted by Tony Valko

Will any of the number strings start with leading zeros?

Data Range

	A	B
1
2	Line1,ABCDORG,1234567,Test Data, Test Addrress	1234567
3	Line1,ABCDORG,15485,Test Data, Test Addrress	15485
4	Line1,ABCDORG,68787,Test Data, Test Addrress	68787

This formula entered in B2 and copied down:

=--TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",100)),200,100))

No leading zereos. Hows doe sthe 'REPT' function fit in with this formulae?

Originally Posted by oeldere

maybe like this.

but I doubt it is easier.

Thanks mate. :D

Thanks again, much appreciated.