Formula to exact customer number only

[tantcu]

Hello,

I have a list of customer including customer name and customer number. Is there a way to exact customer number out only? This is the example list.

WAL-MART STORES INC - 1028
KOHL'S - 28983
JOHN'S PET PRODUCTS - 3692
PET SUPERMARKET, INC. - 2298
COSTCO WHOLESALE - INTERNATIONAL - 2783
LOBLAW CO-WESTERN GROCERS - 7424

All I want is the number behind the dash. There are few customers that have more than 2 dashes.

Thanks,

[XOR LX]

Hi,

Is the number required always at the very end of each string?

Regards

[AlKey]

Try this in B2 copy formula and drag it down

=TRIM(RIGHT(SUBSTITUTE(A2," -",REPT(" ",255)),255))

	A	B
2	WAL-MART STORES INC - 1028	1028
3	KOHL'S - 28983	28983
4	JOHN'S PET PRODUCTS - 3692	3692
5	PET SUPERMARKET, INC. - 2298	2298
6	COSTCO WHOLESALE - INTERNATIONAL - 2783	2783
7	LOBLAW CO-WESTERN GROCERS - 7424	7424

[Motu040602]

Try
=RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
Good luck

[Tony Valko]

Maybe this...

=MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1234567890)),10)

Note that this returns the number as a TEXT string. This would be necessary if any of the number strings start with leading zeros.

[tantcu]

Thanks everyone. They are all working great!!!

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Tony Valko]

You're welcome. We appreciate the feedback!

[AlKey]

Thanks for the feedback!